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.
| Author |
Topic |
|
calvinkwoo3000
Yak Posting Veteran
98 Posts |
 Posted - 2013-10-06 : 23:47:06
|
| I need to upload about 100 CSV file to MSSQL 2012.The CSV data with double quote and comma as the delimiter.How is the script to run the bulk upload which remove the double quote?I able to upload file by file using Text Qualifier to remove the double quote but how the Bulk Insert to do the same?Example source data:"A0001",7,"20020909"Example data need to upload:A0001,7,20020909BULKINSERT aaaFROM 'C:\CSV\aaa.CSV'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',FIRSTROW = 2)GO |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
calvinkwoo3000
Yak Posting Veteran
98 Posts |
Posted - 2013-10-08 : 00:29:52
|
quote:
Originally posted by visakh16
you can use BULK INSERT,Export Import Task or even OPENROWSET for these. If you want you can specify the delimiter etc information using a format fileseehttp://technet.microsoft.com/en-us/library/ms175915.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Dear visakh16,Thank for your support.I prefer Bulk Insert which i can write the script to run all the CSV file in 1 time execution so that i no need to import CSV file 1 by 1.SOme of the CSV file have about 100 field,if i want to upload with format file, do i need to set all the field 1 by 1 for all the field and all the CSV file? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-08 : 00:46:09
|
quote:
Originally posted by calvinkwoo3000
quote:
Originally posted by visakh16
you can use BULK INSERT,Export Import Task or even OPENROWSET for these. If you want you can specify the delimiter etc information using a format fileseehttp://technet.microsoft.com/en-us/library/ms175915.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Dear visakh16,Thank for your support.I prefer Bulk Insert which i can write the script to run all the CSV file in 1 time execution so that i no need to import CSV file 1 by 1.SOme of the CSV file have about 100 field,if i want to upload with format file, do i need to set all the field 1 by 1 for all the field and all the CSV file?
yes. Format file should contain details on each and every fields------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
BULK INSERT