Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to import a large csv file into Mysql. Unfortunately, the data within the file is separated both by spaces and tabs.

As a result, whenever I load the data into my table, I end up with countless empty cells (because Mysql only recognizes one field separator). Modifying the data before importing it is not an option, as I'm working with something like 400 million rows.

Here is an example of the data:

# 1574    1 1 1
$ 1587    6 6 2
$115 1878    8 9 23

(Where the second and third value of every row are separated by a tab)

Any ideas?

share|improve this question
    
Why is modifying a file on sitting on a hard drive not an option? perl -i -pe 's/\s/|/g' your_file_name for example would transform every space or tab in the file into a pipe | character, which seems like a pretty straightforward solution, because then you'd have a single delimiter for MySQL to work with. –  Michael - sqlbot Oct 9 '13 at 4:20
add comment

2 Answers 2

If you created the CSV from an Excel spreadsheet, I know Microsoft Office is notorious for not pasting/exporting correctly. Try pasting as plain text, and then exporting as CSV. Then, import that into MySQL as tables. Hope this helps, but I don't know enough about where you got the CSV from.

share|improve this answer
    
If he's managing 400 million rows in Excel then he as bigger problems than delimiters. –  mdahlman Dec 8 '13 at 21:51
add comment

If you're on *nix - check out the tools sed, awk, grep and split and related (or even vi). As mentioned perl could do it, so could python or PHP (or C or Java or ...) This looks more like a task for programming tools rather than a database. That's not to say you couldn't do this using PL/SQL or T-SQL or , but sometimes the most suitable tool is not within the database server (and in this case, certainly not within MySQL).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.