[Solved] Access fixed-width text file

[spursystarman]

HI, I have a .txt file which is in fixed-width column format. I have been editing it in notepad++. The file originally contained over 2 million lines but is now about 100,000 lines after that editing session.
However, base doesn't seem to recognise this format at all. Its default seems to be .csv or some other field delimiter but I need to keep to the fixed-width format. Is this possible?
Thanks for any help.

[Villeroy]

Either you import the file into Calc and then copy to your database or you use a database engine that supports text files. Base's HSQLDB can import text files.
With HSQL you would create a linked text table with one column and a view splitting up the columns and convert data types not SQL compatible.
When you copy the view onto the target table, you get the data properly imported.
With a MySQL connection the procedure would be different.

[spursystarman]

OK, quick update. That txt file is now down to a final ~50000 lines. Going to calc and importing it as a fixed-width file causes problems (calc recognises the format because it splits the fields properly) - the problem being that it is clearly struggling with the number of rows because the 'timer' spins round for ages, and when it stops spinning I just get returned to the same 'what file do you want to import' screen that I originally 'imported' from - and nothing is imported.

[UnklDonald418]

You can connect an Embedded Base database to the text file. From the menu select Tools>SQL and execute something like

Code: Select all

CREATE TEXT TABLE "SomeTableName"(
"LineOfText" VARCHAR(256)
);
SET TABLE "SomeTableName" SOURCE "YourFileName.txt ; fs=\n"

Then from the menu select View>Refresh Tables
You should now be able to view and even edit data in the table and any changes will be reflected in the text file when Base is closed.

If you want to import the data into the database use Copy and Paste.
In the Copy Table dialog that pops up give it a new table name and select options
Definition and data
Create Primary Key
then push the Create button.
Right click on the new table name and select Edit.
Make sure the Auto Value field property is set to Yes for the ID (Primary Key) field.

[spursystarman]

Thanks all, I will actually keep this as useful anyway - but I have devised another method which I have now got into the procedure of. But all the same, I can see your method is better!

[Villeroy]

OK, quick update. That txt file is now down to a final ~50000 lines. Going to calc and importing it as a fixed-width file causes problems (calc recognises the format because it splits the fields properly) - the problem being that it is clearly struggling with the number of rows because the 'timer' spins round for ages, and when it stops spinning I just get returned to the same 'what file do you want to import' screen that I originally 'imported' from - and nothing is imported.

The count of lines is not relevant. When importing text, the one and only thing that matters it the actual structure of the text file. Unless you post a typical line of text with a description about the field types you are the only person on the planet able to implement a working import routine.

viewtopic.php?t=109195 should work with fixed-width text just as well. The gory details depend on your SQL skills.

[Villeroy]

Thanks all, I will actually keep this as useful anyway - but I have devised another method which I have now got into the procedure of. But all the same, I can see your method is better!

and of course, you won't share your method with anybody.

[spursystarman]

...and they say irony is dead.
I didn't mention my method because of the reason I stated - the method you guys described was better!
However, for the sake of completeness, it's as follows.
1) Copy a batch of 1000 records and paste into a new .txt file
2) Go to MS access (access 97!) and import this into a blank table as needed (skip fields that are not needed, etc)
3) Perform queries on this table to list only the needed records (usually cuts the record count by about a half)
Then basically repeat, performing step 3 before appending to the previous list.

Yes, I could write a VB app to do all this but since the process is already in place, I'm happy to stick with that.