Mysql – load csv file into thesql using batch

I just tried a quick test that replicated your problem.

I created a 1 line CSV in Word (which uses smart quotes) as test.csv “123”,“4,5,6” and it opened in Excel as you described.

Try replacing “ and ” with "

Having played with your sample I notice that Excel does not like the spaces between fields

e.g. instead of

"20051", "", "2009 Sep 30 02:53:23", ...

you want

"20051","","2009 Sep 30 02:53:23",...

a decent Regular Expression replacement should be able to handle it with

Find:    |("[^"]*",) |
Replace: |\1|

(pipe characters for visual cues only)

Or simply modify the .Net code if you have access to it ;-)

Also, as Arjan pointed out, you may also need to convert the file from UTF-8 to ANSI to prevent cell A1 from containing the BOM and its surrounding qoutes.

I have come across the Catch 22 of ANSI encoded CSV not handling international characters and UTF encoded CSV not being propery handled by Excel; and not found a solution while mainting the CSV. If international character support is required, the XML (or native XLS) formats seem the only way to go—at the cost of simplicity.

What you need isn't Excel. Excel and Calc (OO.o) can both open CSV as spreadsheets, but what you want is something to manipulate the text file into a properly-formatted CSV file.

For this you want a good text editor. There are very few text editors that allow you to do multiline search and replace (don't know why, as this is an incredibly useful feature and easy to implement). But a very good free editor that (sorta) has this functionality is Notepad2. It is basically an upgraded version of Windows' default Notepad, but not quite as bulky as Notepad++. You can replace Windows Notepad with Notepad2 or Notepad++, but that's beyond the scope of this answer.

After installing Notepad2:

1. Open up the text file and hit Ctrl + H to bring up the find & replace dialog.
2. Type a comma or comma and space into the Search String box.
3. Now you'll notice that the Replace with: box is only a single-line. That's OK because Notepad2 supports regex right out of the box. So check the box that says Regular expression search.
4. Despite what the above checkbox reads, it's actually Regular expression search AND replace—meaning that you can use regexps in the Replace box as well. So in it, type \n and hit Replace All.
5. Then just save as *.csv and quit. If you open it up in Excel/Calc, it should now be a column of names/e-mails.

Edit:

Actually, re-reading your question, it occurs to me that you may want 2 columns: 1 for email, one for name. If that's the case, then it's a little more difficult to do than just replacing all commas with line-breaks. But fear not! Regexps are here to help once more:

1. Open up the original text file.
2. Pull up the search & replace dialog.
3. Type this into the search box: \([^,]+\),\([^,]+\),
4. Type this into the replace box: \1,\2\n
5. Hit Replace All.
6. Save as *.csv and quit.

You may optionally modify the regexps to put a space after the commas if that is how the text file is formatted or how you want it formatted, e.g.

first last, user@domain.com, bob builder, bob@builder.com, foo...
versus:
first last,user@domain.com,bob builder,bob@builder.com,foo...

Edit2:

For the Notepad++ flavor of regexp, you don't need to escape groupings with backslashes, so the corresponding search regexp would be:

([^,]+),([^,]+),

If there are empty email addresses:

([^,]+),([^,]*),     // assuming that the email is the second parameter
([^,]*),([^,]+),     // make the first parameter optional

Edit3:

To deal with completely missing fields, it gets a little messy. This is because neither Notepad2 nor Notepad++ support optional groups or negative lookbehinds. So we need to apply 2 different regexps:

1. This takes all consecutive e-mails and injects an empty field (, ,) between them. Run this (with Replace All) multiple times until you stop getting matches.:

   ([^,]+@[^,]+), ([^,]+@[^,]+),*   // search for
   \1, , \2,                        // replace
   

2. Manually add the extra empty field at beginning or end of document if needed.

3. Depending on whether the e-mail comes first or name comes first, replace either of these:

   ([^,]+), ([^,]*),\s     // email first
   ([^,]*), ([^,]+),\s     // name first
   

   with:

   \1, \2\n
   

And that should do it. The \s above is just a space character. You can either put a space there or write \s in the regexp.

Note: I normally try to avoid asking a person to install a new text/graphics/audio/video editor just to solve a specific problem, but this is one instance where: 1.) it's a very lightweight (~300KB) free program that I know works; 2.) it's incredibly useful and almost anyone who hasn't already installed a notepad replacement like Notepad2/Notepad++ would benefit from installing it. In fact, I install it on every Windows computer I use, and also every Windows workstation at my work.