I ran into an interesting situation the other day when trying to import some data into Excel. I had a text file with words and definitions that I needed to have in two columns in Excel, one for the word and one for the definition.

The words and their definitions were separated by a hyphen (-), so I could have gone ahead and done a direct import and specified the hyphen as the delimiter. The tricky part was that the hyphen appeared in some of the definitions too, and so using it as a delimiter would have split some of the definitions into separate columns, and I would have had to go through the sheet and reunite the definitions. Given that I had over 10,000 entries, this was a less than optimal solution, and not very time efficient.

I work a lot with Excel and Excel formulas, but I am by no means an Excel guru, and I am sure there’s a pretty simple solution, maybe a macro or something, for this kind of situation. But I used a formula to solve my problem as explained below:

What I did was paste the contents of my text file into one column with no separation between word and definition, with each entry in it’s own row. I then wanted to split the column into two, one with the word and one with the definition. I then used the right, left, len, and find functions. So with my data in column A, I put the following formulas columns B and C respectively and filled down.

=LEFT(A1,FIND(”-”,A1)-1)
=RIGHT(A1,LEN(A1)-FIND(”-”,A1))

This worked perfectly, and solved my problem in seconds. Do you have any suggestions for a different way to do this?

In response to my post on importing large files into Excel by first splitting them, one of my readers, JP pointed out to me that you can bypass the splitting step by using a VBA macro to do the import.

As you may know, Excel has a cut off of 65,536 rows, and so if you want to import a csv or text file that has more rows than that, you’ll run into trouble. This is where the csv splitter I mentioned before, or this macro that JP pointed out to me will come in handy.

You can find the knowledge base article with the macro code, for a VBA solution to importing large files into Excel at http://support.microsoft.com/kb/120596.

And be sure to visit JP’s excellent website and blog VBA Code for Excel and Outlook where you’ll find a lot of useful macros and articles.

If you have a large csv file that you have tried to open in Excel, you know how troublesome that can be, because Excel is limited in the number of rows and columns of data it can handle - 65,536 rows of data and 256 columns per worksheet. Truncation of rows or columns in excess of the limit is automatic and is not configurable.

I discussed this problem before in the post Splitting large csv files - the CSV Splitter where I introduced you to Scorpion’s nifty little program, the CSV splitter, which takes large csv files and splits them into separate smaller files and you decide how many rows you want each file to be. Previously, as you will see if you read my post on Splitting large csv files, you had to register on the forums to be able to download it. Scorpion just updated me that he has now provided a direct download link so that you no longer have to register but can download the csv splitter directly.

You can now download the csv splitter directly here.

The program is easy to use and is a lifesaver if you are like me and frequently work with large csv files. You can Splitting large csv files - the CSV Splitter read more about the csv splitter, and please do leave a thank you for Scorpion.

In previous posts we’ve discussed getting your csv files into sql format using different methods. I walked you through the process from csv to sql using HeidiSQL and PHPMyAdmin. I then did a follow up on how to import a large csv file into a MySQL database using the MySQL command prompt. In all these instances, what we were essentially doing was creating queries, just in different ways.

Well, today I want to present you with another method of getting your csv file into sql, using PHP code. For this piece of code, the full credit goes to legend. You need to make sure the database is already created before you dump the date.

/********************************************************************************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************************************************************************/
$databasehost = “localhost”;
$databasename = “test”;
$databasetable = “sample”;
$databaseusername =”test”;
$databasepassword = “”;
$fieldseparator = “,”;
$lineseparator = “\n”;
$csvfile = “bbqrest.csv”;
/********************************************************************************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don’t set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************************************************************************/
$addauto = 0;
/********************************************************************************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************************************************************************/
$save = 1;
$outputfile = “output.sql”;
/********************************************************************************************/

if(!file_exists($csvfile)) {
echo “File not found. Make sure you specified the correct path.\n”;
exit;
}

$file = fopen($csvfile,”r”);

if(!$file) {
echo “Error opening data file.\n”;
exit;
}

$size = filesize($csvfile);

if(!$size) {
echo “File is empty.\n”;
exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = “”;
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line,” \t”);

$line = str_replace(”\r”,”",$line);

/**********************************************************************************************/
This line escapes the special character. remove it if entries are already escaped in the csv file
***********************************************************************************************/
$line = str_replace(”‘”,”\’”,$line);
/**********************************************************************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode(”‘,’”,$linearray);

if($addauto)
$query = “insert into $databasetable values(”,’$linemysql’);”;
else
$query = “insert into $databasetable values(’$linemysql’);”;

$queries .= $query . “\n”;

@mysql_query($query);
}

@mysql_close($con);

if($save) {

if(!is_writable($outputfile)) {
echo “File is not writable, check permissions.\n”;
}

else {
$file2 = fopen($outputfile,”w”);

if(!$file2) {
echo “Error writing to the output file.\n”;
}
else {
fwrite($file2,$queries);
fclose($file2);
}
}

}

echo “Found a total of $lines records in this csv file.\n”;

?>

So that is one nice easy way to do it, and the code is easy to follow and understand, so that you can mod and adapt it to your needs. Many thanks to legend for this code.

I recently came across a website that has a bunch of free database tools. These are PHP scripts. I haven’t tried any of these tools so I can’t really vouch for them.

The website is http://scripts.ringsworld.com/database-tools/. If you have used any of the tools listed on this page, please leave a comment and let us know.

Happy coding!

I recently did a series of posts on how to convert a csv file into a sql file. You can see a summary here that will lead you to the other posts.

In that series I talked you through doing the import using HeidiSQL and then creating the sql file using phpMyAdmin, a process which is easy and straightforward to use. However, for those who like to use the MySQL command prompt, there is another way to go through this process, using the LOAD DATA INFILE command.

What you do is get to your MySQL command prompt and log in as root. Create the database and the tables using the same basic process I outlined before in my newbie tutorial about importing large sql dumps. In summary:

1. Create the database:

3. If this is done correctly with no typos, you will get a success statement to the effect:

Query OK, 1 row affected <0.00 sec>

4. Switch to the database you just created using the USE command.

You should see the text

Database Changed

Then create the table using the create table command:

CREATE TABLE “table_name”
(”column 1″ “data_type_for_column_1″,
“column 2″ “data_type_for_column_2″,
… )

Remember that you want your columns to correspond to the fields and data types in your csv file.

Once the database and table are created, you are ready to import your csv file. Remember that you can use this same process for any other kind of delimited file, whether it uses commas, semicolons, or other types of delimiters. To import the csv or other delimited file into MySQL, use the LOAD DATA INFILE command. This command has several options and things you need to consider. Here is the basic syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name’
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS
[TERMINATED BY ’string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES
[STARTING BY ’string’]
[TERMINATED BY ’string’]
]
[IGNORE number LINES]
[(col_name_or_user_var,…)]
[SET col_name = expr,…]

This may look a little confusing, but it really isn’t. The stuff in square brackets [] is your options that are determined by your source and your data, and a lot of them may be optional and unnecessary. If you study the code carefully you will recognize a lot of the same options we encountered when using HeidiSQL to import the file, for example

[FIELDS
[TERMINATED BY ’string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]

for the example file that we used in the previous tutorials would be:

[FIELDS
[TERMINATED BY ‘,’]
[[OPTIONALLY] ENCLOSED BY ‘”"‘]
[ESCAPED BY ‘/n’]
]

Does this look familiar? See if you can redo the process of importing the csv file that we went through in the tutorial but this time using the MySQL command line. This is a useful exercise because you may be faced with a situation where the command line is all you have. So have a go at it!

I recommend that you read up on the syntax of the LOAD DATA INFILE command at http://dev.mysql.com/doc/refman/5.1/en/load-data.html. This is for v.5.1, so if you are using a different version, please check to make sure the syntax is correct.

If you have any difficulties, questions, comments, or have noticed some errors in this tutorial please do leave a comment. I will greatly appreciate it!

Happy Coding!