When I use the import function to import data from a textfile with 1200 records with fields delimited by '|', only 101 records are imported. The records that are imported are imported correctly but the process seems to ignore everything after line 101. The format of the file seems consistent throughout with nothing odd at line 102. All the other functions I have tried work OK. Can anyone suggest how I can overcome this problem? MysqlMan is set up on an Apache server. I can provide more information about the problem if required. Any suggestions gratefully received!
Aug 27, 2000, 5:56 PM
Veteran (6956 posts)
Aug 27, 2000, 5:56 PM
Post #2 of 5
Views: 3908
Are you sure the file you are importing is ok?
you might want to carefully look at those records and see if
anything is funny about them --- proper line termination, correct
number of fields, etc. Start at the last record properly imported,
then go on from there.
If it looks ok, make a copy of the import file, delete the first 100
lines, then import the file again.
If only 1 line imports, you'll know it's something in the import file.
If only 101 lines import then something else is going on.
I know I've used MySQLMan to import 600+ records, and as many as
3000 short records.
http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/
you might want to carefully look at those records and see if
anything is funny about them --- proper line termination, correct
number of fields, etc. Start at the last record properly imported,
then go on from there.
If it looks ok, make a copy of the import file, delete the first 100
lines, then import the file again.
If only 1 line imports, you'll know it's something in the import file.
If only 101 lines import then something else is going on.
I know I've used MySQLMan to import 600+ records, and as many as
3000 short records.
http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/
Aug 28, 2000, 11:29 AM
Novice (9 posts)
Aug 28, 2000, 11:29 AM
Post #3 of 5
Views: 3898
Thanks to "pugdog". Yep it was the file. It contained a field which originated as an MSAccess memo field whose contents contained an assortment of line-feeds, carriage-returns #s and []s in some records. It has taken me most of the day to sort out - importing and exporting between Access, Excel and Word, using the find and replace 'special characters' facility in Word, copying cells to Excel, re-importing into Access, exporting to text file, importing into "Programmers File Editor" saving as a Unix file and re-importing. Eventually imported all 1265 records successfully!
Aug 28, 2000, 12:26 PM
User (281 posts)
Aug 28, 2000, 12:26 PM
Post #4 of 5
Views: 3890
Hallo Pduerden,
Eh... did you use Access as last step to export the file as a text file/tab file ?
Or did you use Excel and it as a tab file ?
Just wanna know it this because i am also having some export MS Access 2000 MEMO field problems. And i think export as .tab file from excel will work for me.. Just wanna know what your 'last' step of exporting was and what type a file you used .txt or a tab file ?
Allready thanks.
Regards Startpoint.
Eh... did you use Access as last step to export the file as a text file/tab file ?
Or did you use Excel and it as a tab file ?
Just wanna know it this because i am also having some export MS Access 2000 MEMO field problems. And i think export as .tab file from excel will work for me.. Just wanna know what your 'last' step of exporting was and what type a file you used .txt or a tab file ?
Allready thanks.
Regards Startpoint.
Aug 28, 2000, 4:01 PM
Novice (9 posts)
Aug 28, 2000, 4:01 PM
Post #5 of 5
Views: 3882
Hi startpoint
The last step was to export from Access to a textfile, field delimiter '|' , text qualifier "none".
I also had a problem with dtaes which I resolved like this:
changed the field type in date fields to text then ran an update query to re-arrange the date to yyyy-mm-dd format (e.g for field AuctionDate ("dd/mm/yyyy" in Access): Mid([AuctionDate],7,4) & "-" & Mid([AuctionDate],4,2) & "-" & Mid([AuctionDate],1,2)).
I imported the 'text-style' date as above to mysql. i actually had the column spec for the fields containing dates in mysql set to text, then after importing the records, changed the column spec to date whcih seemed to work ok. Though thinking about it now, I could proably have imported the text-style date directly into mysql columns set to date format, i'm assuming mysql reads a text string that looks like a date as a date - not sure about this.
hope above helps
The last step was to export from Access to a textfile, field delimiter '|' , text qualifier "none".
I also had a problem with dtaes which I resolved like this:
changed the field type in date fields to text then ran an update query to re-arrange the date to yyyy-mm-dd format (e.g for field AuctionDate ("dd/mm/yyyy" in Access): Mid([AuctionDate],7,4) & "-" & Mid([AuctionDate],4,2) & "-" & Mid([AuctionDate],1,2)).
I imported the 'text-style' date as above to mysql. i actually had the column spec for the fields containing dates in mysql set to text, then after importing the records, changed the column spec to date whcih seemed to work ok. Though thinking about it now, I could proably have imported the text-style date directly into mysql columns set to date format, i'm assuming mysql reads a text string that looks like a date as a date - not sure about this.
hope above helps