Import CSV File - Help

Databases
1

Hi Guys,

I have over 25 million records to import to a table. How ever the data is set out as the following:

WA2 8PR, Unit 3, Winstanly Ind Estate, Long Lane, Warrington, Cheshire,
SK11 8EQ, 1, Westbond Street, Macclesfield, Cheshire
WN7 2HX 1, Wensleydale Road, Leigh, Lancashire

I’m currently uploading the 1.88GB csv file to the server and going to be using the LOAD DATA query to do it, how ever what do i put to as Columns enclosed with and Columns escaped with? Also do i put Lines terminated with as /n ?

Thanks

2

You don’t have your columns enclosed in anything and from the sample provided there is no way to tell what character you have used to identify commas in the actual data.

3

Hey,

Columns are separated by commas, is there a way of be importing the CSV with just the commas?

If not is there any kind of program i can use that will convert the CSV file so i can easily import the CSV file to MySQL?

Any help would be great.

Thank you.

4

Is there any kind of program i can use that will do what it needs to do to make the the data import to a MySQL Table?

If theres anything you can think of that will do what i need it to do that would be great.

Thank you.

5

An example to get you started. The following has columns enclosed in “” with both address lines in the one column. Because the columns are wrapped in quotes the comma in the data doesn’t need to be escaped.

“WA2 8PR”,“Unit 3”,“Winstanly Ind Estate, Long Lane”, “Warrington”," Cheshire"
“SK11 8EQ”,“1”,“Westbond Street”, “Macclesfield”, “Cheshire”
“WN7 2HX”,“1”,“Wensleydale Road”, “Leigh”,“Lancashire”

This is the format that Excel expects CSV files to be in so that it can load the data correctly. The alternative if you don’t want to wrap all the column content would be to escape any commas in the data itself - otherwise where the column is supposed to contain a comma the content would be jumbled.

6

Lines terminated by
. Optionally enclose with ". Separated by ,. As felgall has brought up, you may run into a problem since it doesn’t seem you have enclosures. Double check after import.

7