Using Access to convert Format 2

[jeanna143]

Hello Everyone,

I need to convert a tab delimited file to field list format. I had a similar case where we went from a list to spreadsheet form in Access. But, I'm not sure if this can be reverse engineered. The data from one system will be extracted in Tab delimited format. The system it needs to go to will only except 'Field List Format', which looks like this:
00000000000000ZINVCD I173
00000000000000ZSYSID PIMS
00000000000000ZORG IMP BOND

00000000000000ZINVCD is the field and I173 is the fields value. Each field repeats this way and then starts over when a new record starts.

Any help would be greatly appreciated!!!

Thanks,
Jeanna

 

[bboffin]

Try using the File|Export option in Access and select the 'Save as type' as Text Files. Don't check the 'Save Formatted' box.

You will be presented with a dialog. Select the 'Fixed Width' option and save the file.

This should be pretty close to what you want.



Bob Boffin

 

[KenReay]

Hi

Should be possible to do this with a little VBA

Import the tab delimited file into a table. Ensure the column names in the table match up with those you wish to appear in the output

Ensure you have a reference to the DAO library

Use DAO methods to extract column name and column value from the recordset, and write them out to a text file.

Do you need more ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jeanna143]

Ken,

You wouldn't happen to have an example of this would you?

Thanks,
Jeanna

 

[jeanna143]

Bob,

I don't see 'Fixed Width' as an option to save in.

The 'Save Formatted' box is grayed out.

Do you need a plug in for this?

thanks,
Jeanna

 

[KenReay]

Hi

Will this get you started?

Private Sub Command0_Click()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
'
'DoCmd.TransferText acImportDelim, , "MyTable", "myFile", False
'
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("SELECT * FROM MyTable;")
Open "TESTFILE.txt" For Output As #1
Do Until Rs.EOF
Write #1, Rs(0).Name, Rs(0).Value
Rs.MoveNext
Loop
'
Close #1
Rs.Close
Set Db = Nothing
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

OOPS, left a cooment in by mistake

Private Sub Command0_Click()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
'
DoCmd.TransferText acImportDelim, , "MyTable", "myFile", False
'
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("SELECT * FROM MyTable;")
Open "TESTFILE.txt" For Output As #1
Do Until Rs.EOF
Write #1, Rs(0).Name, Rs(0).Value
Rs.MoveNext
Loop
'
Close #1
Rs.Close
Set Db = Nothing
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

or probably nearer still to hwta you want:

Private Sub Command0_Click()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
'
DoCmd.TransferText acImportDelim, , "MyTable", "myFile", False
'
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("SELECT * FROM MyTable;")
Open "TESTFILE.txt" For Output As #1
Do Until Rs.EOF
'
For i = 0 To Rs.Fields.Count - 1
Print #1, Rs(i).Name & Rs(i).Value
Next i
Rs.MoveNext
Loop
'
Close #1
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jeanna143]

Hello Ken,

I didn't catch this when I read your first post "Ensure the column names in the table match up with those you wish to appear in the output"

Unfortunately, the two systems call the same fields by different names. Is there a way around this?

Thanks,
Jeanna

 

[jeanna143]

I tested the script with a simple file and here are the results.

myFile.txt, tab delimited text file with one record
Number Field 2 Requester Purpose User Type Req. status
REQ029855 SMITH, MONICA TEST5 SMITH, MONICA E-Procurement Satisfied

myTable that was created
F1 F2 F3
"Number Field 2 Requester Purpose User Type Req. status"
"REQ029855 SMITH" "MONICA TEST5 SMITH" "MONICA E-Procurement Satisfied"
It's hard to see here, but all of the field headings went into F1 row 1, nothing in F2 or F3 and then data was spread across F1 - F3 in Row 2 with Tabs between data in the columns.

TESTFILE.txt output
F1Number Field 2 Requester Purpose User Type Req. status
F2
F3
F1REQ029855 SMITH
F2MONICA TEST5 SMITH
F3MONICA E-Procurement Satisfied

It seems something is really causing all the fields to be off.

Thanks,
Jeanna

 

[KenReay]

Hi

If you look at help for DoCmd.TransferText, you wil see that the second parameter is an specification. This will allow you to specify the import file as having (or not having) field headings. It is a little difficult (for me) to describe how to create a specification. It is essentially a wizard, but it is not very intuitive to get into it, essentially choose File \ Get External Data \ Import. When you get the dialogue box asking for a file names, change the files of type to "Text (.txt, .csv..)", and choose the file you want to import. On the next form, choose the button Advanced, this will allow you to save the specification, you can then use that specification in the DoCmd to further vary the parameters.





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JerryKlmns]

jeanna143,

Your field names might be of different length size, making it difficult to export as you wish.

If all the fields are of length size 1-25 maybe this

Print #1, left(Rs(i).Name & String(25, " "),
25) & vbTab & Rs(i).Value

will get you through.

 

[jeanna143]

Thank you!

Does anyone know if there's a way to left justify some of the text in the print statement, but not all of it? Basically the left column should be left justified and then a tab and then another left justified column.

Thanks!
Jeanna