Importing txt file (no delimited) into a database

[Charles L. Cotton]

I need to get a rather large amount of data into a database. I'll probably use Access, but I could also use MySQL5 on one of the servers. I really would like to import this file, rather than entering the data manually for about 7700 records.

Here is my problem. The data is available only in a txt file and the file has no delimeters between the fields. Rather, each record is a fixed length of 196 bytes with specific data contained in specified blocks. For example, the full name (last, first, middle) is contained in "position" 1-31, address first line is in 32-63, city is in 64-84, etc. I know a delimited file (comma, tab) can be imported into a database, but can a file like I just described be imported into either Access or MySQL? If it matters, there are no characters acting as placeholders. If there is no data in a given area it's simply left blank.

Thanks,
Chas.

[fenster]

perhaps you could use Excel.

open the .txt file in Excel. highlight column A (first column), then go to Data->Text to Columns.
choose the 'Fixed Width' option then hit 'next'. you will then be able to place the column borders where you want them, then finish and save as an excel sheet.

I don't use Access, but I'm guessing it should be fairly simple then to import the newly created excel sheet into a table.

[Xander]

Actually, you can also do what fenster suggested directly into Access, and skip the Excel step. There's an option to import external data, and you can point it at your text file, specific that it's fixed with and define the field lengths and suck it all right into a new table.

[Charles L. Cotton]

Actually, you can also do what fenster suggested directly into Access, and skip the Excel step. There's an option to import external data, and you can point it at your text file, specific that it's fixed with and define the field lengths and suck it all right into a new table.

That's great news! I'm working on the Excel method and it will work, but the txt file is about 2.5 times as large as the Excel 65,536 record limit. I was shocked to see it's that large! I thought I was going to have to import it in segments.

Do you know if there is a record limit in Access?

Thanks,
Chas.

[hi-power]

Do you know if there is a record limit in Access?

It's not a record limit you have to worry about, but a .MDB data file size limit of 2GB.

I use Access every day and haven't hit that limit yet. I've also imported quite a few files with over a million records.

[Charles L. Cotton]

Do you know if there is a record limit in Access?

It's not a record limit you have to worry about, but a .MDB data file size limit of 2GB.

I use Access every day and haven't hit that limit yet. I've also imported quite a few files with over a million records.

I'll have about 160,000 records, so Access should work fine.

Thanks for your help guys!
Chas.

[BigBlueDodge]

Charles, I'm not sure what version of Excel you have, but the latest version of Excel supports up to 1 Million rows in a spreadsheet from the previous 65K.

[jimlongley]

If not importing directly into Access, which I haven't used in a couple of years so my recall is rusty, you could also take the .txt file into Word, and either create a table or add delimiters using find/replace.

[Charles L. Cotton]

I was able to import it into an Access database without any trouble.

Thanks,
Chas.