CSV File to SQL Server from asp.net - URGENT

Question

Hi all..

How to insert and update data from CSV File to SQL Server table from front - end.

I have the CSV File, but it converts the numeric values to scientific format.

Eg -
357449050348687 gets converted to 3.57994E+14.

How to stop this conversion and how to insert this data into sql server table ??

Please help..

It is urgent.

Ramesh Maruthi · Answer

http://www.solveyourtech.com/how-to-open-a-csv-file-in-google-docs/ use the above link and import ur file and edit the columns as you want and download it as comma separated values.... https://support.google.com/mail/answer/12119?hl=en http://www.computerhope.com/issues/ch001356.htm

Riddhi Valecha · Answer

Hi... What is google spread sheets ?? I did not get any thing from google. Please explain step-wise and in detail. Thanks a ton in advance......

Ramesh Maruthi · Answer

Riddhi That's what I said even I faced the same problem before a month ago I used google and found the answer just open and edit your file with google spreadsheets or open office than save it. It solves ur problem :)

Riddhi Valecha · Answer

Hi... Yes... I did this. This works with excel files (xls and xlxs formats). But it does not work with .csv files.

Ramchand Repalle · Answer

Hi Riddhi Have you changed the format of the number column in Excel before saving the file in csv format?  2. select the entire column right click and click on Format Cells and change the type to Text and the save the file Thanks Ramchand

Riddhi Valecha · Answer

Hi.. When I save an excel with csv file extension i.e. in Save As dialog box I choose CSV(Comma Demilited) the file is saved as Book1.csv. When I open the file the number is again in that scientific format - 20.678945E+56. I have to update and insert data from csv files from front-end (using File Upload control). And the system throws an exception - Data not in correct format. Also if the system reads the data it will insert wrong data in the sql server. How to overcome this issue ??

Ramesh Maruthi · Answer

Hey Riddhi Try to use the google spread sheet for editing the csv column and try to change the format from scientific notation to general.

Ramchand Repalle · Answer

Hi one way to do through SQL Server GUI 1. open csv file in excel 2. select the entire column right click and click on Format Cells and change the type to Text and the save the file And then while uploading into SQL Server from front end you can choose the Data Source as flat file and select the file type .csv then you can do the import operation as usual.. If you are doing from code level in Visual studio. The problem is while reading data in csv file By default its considers as number format based on the data in your example. If you want to insert as it is without changing to exponential. you have to treat that one as string instead of number at code level. This link might be helpful to proceed further csv exponential issue . Thanks Ramchand.