Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 CSV import problem with Money data-type

Author  Topic 

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-08 : 07:08:19
I am developing a web application with Asp.net and C# to import a CSV file into my exisitng tables. I am following this article to import data:
http://www.codeproject.com/aspnet/ImportExportCSV.asp

The problem I am facing is that one of my table contains a money data-type column. Therefore, my application gives me Conversion error.

When I try to import data from csv file into this table, I get:

Disallowed implicit conversion from data type varchar to data type money, table 'MyTable', column 'Total'. Use the CONVERT function to run this query.

The above article that I am following, puts all the data values from the csv file into quotes, such as: Insert into MyTable...... Values ('value 1','23.34'...). Thus SQL cannot convert 23.34 into money.

I think the sql statement should work if I remove the single quote from the money values.. e.g. Values('value 1',23.34...). However, I am not sure if we can identify a csv column as decimal or money data-type.

Any way to solve this problem?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 07:22:18
You shall not embed numbers with quotes!

Insert into MyTable...... Values ('value 1', 23.34, 'eee', ...).

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 07:43:44
select * from information_schema.columns

might help you investigate if the column is decimal or money.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-08 : 07:50:42
Peso, thanks for the reply. I understand this.

However, the problem is that the application creates the Insert statement by going through the csv file. For each data coloumn, I put single-quote around the values. From the csv file, I cannot determine if the value (or the column) is of numeric data-type. If I can determine if the value from the csv file is nemeric, I would not put single-quote around the values.

Regards
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 07:52:59
Is there not a ISNUMBER function or similiar for C#?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-08 : 08:31:01
Yes, I could check if the value is numeric using C#. But I think, that will be very dangerous.

For example, NVarchar column may contain "2". So when I try to check this value using C#, this will pass as numeric value. So mistakingly, I will create the Insert statement as Value(2,...), where as it should be Values ('2'..).

Is there any SQL command or any other way to enforces the SQL Server to insert data without checking the data types?

Thanks for your time
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 08:32:39
Where do you get the values from to begin with?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-08 : 09:07:59
For my web application, I need to build export and import functionality.

1 - Export to CSV
I create a csv file for my table.

1a - For char values, I embed them in double-quotes, e.g. "some values"
1b - For boolean columns: if the returned value is "True", I write "1" in csv files, else I write "0"
1c - For datetime columns, I embed the values in double-quotes, e.g. "01/03/2006 11:04:11"
1d - For Decimal columns, I write the values without double-quotes

Therefore, the general structure of my csv file is:
"some values", "1", "0", 23.34, "01/03/2006 11:04:11"

2 - Import from CSV

Using the same CSV files, I want to import data back into the tables. This is where I am hitting the problem.
I read the csv file and create the Insert statement as: Insert into MyTable (....) Value ('some values', '1', '0', '23.34', '01/03/2006 11:04:11')

But get problems with the '23.34' value, as it should be without the quote.

Regards
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 09:17:02
Why not simply use
REPLACE(ForEveryRow, CHAR(34), CHAR(39))
when importing and creating the INSERT statements?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-08 : 09:24:59
Actually, I am not familiar with this Replace... approach.

Would I use Replace.. in Export or Import ?

What is the significance of Char(34) and Char(39)?

Where can I learn more about this Replace function?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 09:33:00
There must be a string manipulation function in C# that replaces characters in a text.
CHAR(34) is double quote "
CHAR(39) is single quote '

In your application, when you import the data file, do the replacement for every read row, and then save the complete INSERT statement with the replaces delimiters.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -