Shulou(Shulou.com)05/31 Report--

This article is about how to deal with CVS with quotation marks text qualifiers in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The company needs to import data into MSSQLSERVER, but the bizarre cvs file given by the other party looks like this:

Aa,bb,cc,dd,ee

1, "this"

Is a ", 1," this is a ", 1

2, "this is"

"a", 2, "this is a", 2

Ordinary bcp processing can be miserable, and mssql cheats can't automatically distinguish text qualifiers like mysql. "

Studied it and dealt with it in this way:

1. Create the corresponding table in DB

Here) collapse or open

/ * generaate thebcp fmt file*/

Exec xp_cmdshell 'bcp test.dbo.table_1 format nul-t ","-c-x-f C:\ test\ table_1.xml-Utopia cards *-playing games *'

Go

3, modify the xml, please pay attention to the highlights. If you open this with ie, you can also see it clearly. "

In addition, there are these special characters, which can be used in case they are cheated by unreliable pig teammates one day.

The single quotation mark is & apos

Double quotation marks are "

& Yes &

> Yes >

Then remove the title of the first column of cvs. Although Microsoft says that the first row can be specified through firstrow, I used firstrow=2 to start with the second line.

If you don't get rid of it, you will report an error, because you are in a hurry, you will not consider the reasons and specific principles for further study for the time being, and then you will have time to test them slowly.

4. Take the value from cvs. Here I use openrowset for verification. In fact, at this time, you can also directly use bcp plus the specified format file to import.

Click (here) to collapse or open

Select * from

Openrowset (bulk'C:\ Booker\ test.csv',-- the path and name of the file to read

Formatfile='C:\ Booker\ Table_1.xml',-- path and name of the format file

-- fieldterminator=' ","

Firstrow = 1mai-the first line to be loaded

-- lastrow = 1000,-- the last line to load, this value must be greater than firstrow

Maxerrors = 100,-- the maximum number of errors in the load operation before the load fails

-- errorfile ='c:\ wc_error1.txt',-- store the wrong file

Rows_per_batch = 10000-rows imported per batch

) as t

You have the results you need.

That's what it is in text format.

Aa bb cc dd ee

- --

1 this

Is, a 1 this is a 1

2 this is

"a 2 this is a 2

Thank you for reading! This is the end of the article on "how to deal with CVS with quotation marks text qualifier in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.