3

I use the following MS SQL Query to import a text file containing Comma Separated Values into DB:

BULK INSERT logs
FROM 'E:\Sample.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Unfortunately, one of the fields in my CSV Text file contains messages like: "Problem in UDP ports(234,137)" which contains comma within itself. Hence, the DB takes values before comma as one field and inserts the value after comma into the next field (which is logically incorrect). As a result, the legitimate value of the latter field gets discarded while getting inserted into the table.

Hope my problem is best explained.

Any solutions to overcome this problem and store the whole:"Problem in UDP ports(234,137)" into a single field?

Greg Smith
  • 2,449
  • 2
  • 24
  • 37
Navaneeth Krish
  • 35
  • 6
  • How you get csv file? – Aleksandr Fedorenko Apr 07 '13 at 14:14
  • Are the values in your csv file consistently quoted? If not, then you may be having [this problem](http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes). SQL Server seems to expect that either all values in the file are quoted, or none are. For that reason I prefer to pre-process the file with a language that has good csv support (Perl, Python, whatever) and either generate a 'clean' csv file for import, or just generate an `INSERT` script directly. – Pondlife Apr 07 '13 at 15:53

2 Answers2

0

I prefer to run my CSVs through a Microsoft log parser link and converting it to Tab Delimited. That way bulk insert reads it perfectly.

Here's a pretty good link for about using the log parser link. Create your command to read your csv and output a tsv, then call it with xp_cmdshell.

Saves you the trouble of creating format files for various files.

deeg
  • 528
  • 2
  • 5
  • Hi.. Thank you! Few observations: 1) Tried using Log parser. But all my fields come in a single column when viewed in Datagrid. 2) Is there any option available to store the output TSV as a text file format? 3) To your notice, I'm using C# for my front end. Hence, I must be able to read whatever Logparser outputs me.. Any Solutions: – Navaneeth Krish Apr 07 '13 at 01:02
0

Have you tried by putting some other field terminator?

Shreejan sharma
  • 51
  • 1
  • 1
  • 5