| 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 LarssonHelsingborg, Sweden |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-08 : 07:43:44
|
| select * from information_schema.columnsmight help you investigate if the column is decimal or money.Peter LarssonHelsingborg, Sweden |
|
|
|
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 |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
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 |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
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 CSVI 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-quotesTherefore, the general structure of my csv file is: "some values", "1", "0", 23.34, "01/03/2006 11:04:11"2 - Import from CSVUsing 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-08 : 09:17:02
|
| Why not simply useREPLACE(ForEveryRow, CHAR(34), CHAR(39))when importing and creating the INSERT statements?Peter LarssonHelsingborg, Sweden |
|
|
|
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? |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
|
CSV import problem with Money data-type