Import CSV File Data

Question

Import CSV File Data

DesiGuju

Old Hand

Points: 303
More actions
June 5, 2007 at 7:33 pm

#124172

How can I import CSV file data into SQL Server table? I need to insert data from CSV file to table twice a day. Table has more then 10 fields but I only need to insert value into two fields.
Any suggestion?
Thanks In Advance

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

Lots of options. I'd personally look into bcp or DTS. DTS would probably be my first choice, since every time I do design an import method of some sort, we invariably are asked to have the functionality increased, such as error checking, e-mailing rowcounts, performing some command line operation if it fails, etc., at which point the ease of doing it with bcp is eliminated.

If you know the requirements will never change, and if you want a fairly easy solution, then bcp might work for you as well. It's certainly a useful little program at times.

DesiGuju Old Hand Points: 303 More actions · Answer 2

Thanks For the Respose

DTS is not an option.

I can use BCP insert but it will insert data into all columns. I don't see anyway to insert data for specific field in table.

Can you provide me with some example ?

Thanks

David McFarland SSChampion Points: 11815 More actions · Answer 3

I haven't done it in a while, but doesn't putting a zero into the "column sort order" column in the format file skip over that field in the source file?

Jus SSCrazy Points: 2536 More actions · Answer 4

Try using this from QA.

Declare @vchrBulkCmd varchar(500)

SET @vchrBulkCmd = 'BULK INSERT TblCsv

FROM ''c:\Testcsv.txt''

WITH ( DATAFILETYPE= ''char'', ROWTERMINATOR = '''+CHAR(10)+'''

)'

exec(@vchrBulkCmd)

DesiGuju Old Hand Points: 303 More actions · Answer 5

Prasad,

Thanks for the response.

I actully need to update my table with CSV data...twice a day.

I don't think there is a Bulk Update....any other way to work around this ?

David McFarland SSChampion Points: 11815 More actions · Answer 6

Sohil, did setting the column sort order to 0 not work for you?

DesiGuju Old Hand Points: 303 More actions · Answer 7

David,

I did not try seeting the column sort order. Since the requirment change.

Now i need to update value in table from CSV file data...insted of inserting data as new value.

David McFarland SSChampion Points: 11815 More actions · Answer 8

In that case, I'd recommend a staging table. Use that to perform the update.

DesiGuju Old Hand Points: 303 More actions · Answer 9

David,

What is staging table ? Can you give me an example ?

Thanks

David McFarland SSChampion Points: 11815 More actions · Answer 10

A staging table is typically used as a temporary placeholder for data, often from some source other than SQL Server.

The idea is that you truncate the staging table and reload it (or drop and recreate it if that works better for you) every time you have new data to handle. Once it's in the staging table, you either clean it up, insert various portions of it into their final tables, create a new table from it, etc. Basically, pretend that your data feed comes in the form of a SQL Server table, instead of a CSV file, and you have the concept of a staging table.

If you post more details, we can help you with the actual coding, but the above should get you started down the path.

DesiGuju Old Hand Points: 303 More actions · Answer 11

David,

I have table "tblCSVDataUpdate" which needs to be update twice a day from "C:\CSV\CSVUpdate.txt" file

tblCSVDataUpdate has following fields:

OrderNo

Prize

Quantity

CustNo

ProductID

From CSV file i only need to update:

Prize and Quantity

This is what i have so far:

Bulk Insert tblCSVDataUpdate

From 'C:\CSV\CSVUpdate.txt'

With

(

FIELDDTERMINATOR = ',',

ROWTERMINATOR = '\N'

)

How can i only update two fields data from CSV file?

Do i have to run this as stored procedure ?

How can i schedule this to run twice a day ? I'm using SQL 2005

Thanks for all your help

David McFarland SSChampion Points: 11815 More actions · Answer 12

Now that we have more details, a few more questions.

What data in the text file joins to the data in the table so that you can update appropriately? Alternatively, you could just list the columns that are in the text file and we could probably make a good guess.

If the data in the text file doesn't have a corresponding row in the table, do you insert it or just ignore it. In other words, can the table grow based on data found in the text file, or only change?

As for running it twice a day, a stored proc would be fine, as would SSIS (or even command line if you really wanted for some reason). Put whichever you choose into a job, and schedule that job to run whenever you want.

DesiGuju Old Hand Points: 303 More actions · Answer 13

1-OrderNo from the text file will join with OrderNo field in table

2-If the text file does't have a corresponding row in table, we will just ignore it. Text file will only have OrderNo, Prize, Quantity).

3-How can i go about adding coomand line to run the job ?

Thanks

David McFarland SSChampion Points: 11815 More actions · Answer 14

Okay, I'd create a staging table to hold the data from the text file, and for safekeeping, I'd also make a copy of the table you're working with, in case something goes wrong in the process. Then, create a stored procedure that does the following:

1. Truncate the staging table. Be careful that you don't truncate your main table.

2. Does the Bulk insert that you have now

3. Update your main table, joined to the staging table on OrderNo.

Once that procedure is done, create a job with a single step, and have that step call the stored procedure. Schedule the job to run twice a day, at whatever times you need it to.