I have a bunch of data that has been export to a .txt file from MSSQL. It has tab separated fields and line separated records. That much I am sure of. I have the sql scripts that create the tables in MSSQL and have converted them to MySQL script for creating the table in MySQL and it seems to work fine.

Two things. First, dates are putout from MSSQL as xxx dd yyyy hh:mmXM and using

LOAD DATA INFILE filename.txt INTO TABLE table

causes all date fields to come across as 0000-00-00 00:00:00. Also, it seems that there are a good number of records that when they are imported shift the data to the left at different places. I will notice that on one record, the sixth field is put into the fifth field position and the remaining fields are all shifted left, like field five is just dropped. The txt is a rather large file and the record length is a couple tousand characters so it is hard to find the record in the txt file to try to see what is happening.

So, I am hoping that someone might be able to tell me what to look for with regard to the shifting data and also what I might do to handle dates.

Thanks for any help.

Steven

first, import the .txt file data into Excel, check that it is okay, then save as a .csv file (comma seperated plain text).

next, change the MySQL tables so that the date/time fields are text (CHAR/VARCHAR) so as to avoid the date format problem.

next, import the .csv file into MySQL. Now add a new MySQL date/time column and write an SQL query to convert the imported text date/time field to the MySQL date/time field using the MySQL SQL functions.

finally, remove the temporary imported text date/time field.

Mathew,

Thanks for the reply. I should have mentioned that this .txt file is on a Linux server and is something I get everyday via an ftp transfer. I am a real estate broker and this is a dump of MLS listings that I need to import so that I can offer search capabilities to those visiting my site. So, I have no control over what I get, only thatit is suppose to be the same format every time and I need to figure out how to get it into MySQL. I was hoping there was a way that MySQL would be able to deal with the date as it was read in. If not, then I will write an awk script to preprocess it prior to importing it. The idea of importing it into excel to look it over first might help out. It would at least let mee look at the data in some sort of structure to see what might be causing problems. I can certainly get it over to my WinXP machine to look at it, but as for a routine for every day, I do not think I want to do this every day.

Thanks again for the thoughts.

Steven Hill

StevenHill,

You also may not to use the intermediate .txt file to import your data from MS SQL to MySQL. There is a number of special tools, which allow you to pump data directly from one database server to another (so called "data pump"). One of such tools is MySQL Data Wizard, which is available to download at http://www.mysqlmaestro.com/products/download.html. It also supports data import from .txt file, but it uses its own method to import (not MySQL's LOAD DATA command), so probably it won't cause the problems with data shifting and date conversions.