Import CSV to SQL Server 2012 With descriptive fields with commas

Question

Import CSV to SQL Server 2012 With descriptive fields with commas

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a .csv file that I need to import to sql server 2012.
I have already created the table
Then perform the insert.
The problem I have is that in the table there are descriptive fields that have commas within themselves, for example in the domicile column many records have things like

Aguero 3400, CABA

With which it is difficult to divide fields.
I would like to know how to filter these fields as a single column, if there is a non-manual way to delete the commas inside them.

BULK
INSERT tabla
FROM 'C:\datos.CSV'
WITH
(
FIRSTROW=2, 
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
);
GO

sql sql-server

asked by merisavino 17.07.2017 в 09:30

source

2 answers

Field entered that goes down and a new field empty Change value shown by axes

score 1 · Answer 1

Unfortunately both BULK INSERT and bcp if they find a FIELDTERMINATOR even if it were in a text "quoted" for example "Av Lope de vega, al 1300", "CABA" will end up interpreting it as a separator.

If a terminator character occurs within the data, it is interpreted as to terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. Therefore, choose your terminators carefully to make sure that they never appear in your data.

source: link

The optimal alternative would be, as long as you have control over the process of exporting to CSV, using another less common separator, for example the pipe | or replace when generating the commas within each field.

If you do not control the export process you are in a problem, you could eventually import the complete lines into a single field and then apply some quasi-magical logic to each row (verifying number of commas) to be able to detect the invalid comma of the invalid . For example, if the next field of the address has to be a certain list, for example, if it were, let's say Document type (eg DNI, CEDULA, etc) you could assume that the field does not actually end with the comma of "Av Lope de vega, al 1300" but with the one just before DNI or CEDULA , or eventually if the next field is a numeric, you could verify by ISNUMERIC() .

score 0 · Answer 2

Open the CSV file in an editor (Excel for example). Then you save the CSV file again but indicating that the delimiter is a ';' (semi-colon) or another.

If you prefer you can edit the CSV file manually (with the notepad) by changing the commas by another delimiter.

When you import again you indicate what the 'new' delimiter is.