Export table to TXT file using 2 methods

[Neil]

Hi guyz, just trying out this google feature so if i post if in the
wrong area i appologize. now to my question. BTW i'm new to access
programming, i've done a little vb6.0 and vb.net but access vba is a
little confusing for me since i have never used it plus i have lost
touch with vb coding since last 8 monhts.

anywayz, my problem is i'm trying to export a table that's modified by
me which is taken from a file at a prompt, now i have to export the
same file in the same format it came in Fixed and Delimited formats. I
can do this easily using the DoCmd.TransferText acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.txt" . All i do is change
the acExport to Fixed.

The file being imported has fixed width properties. It has 2 types of
records M(main set of record i.e Cust name, ph, email etc) and S(if the
operator upsold items it adds these records w/ corresponding field).
Once it is modified it now has just main record all M's. Now exporting
this format is easy but due to constraints i have to have the new
database output to the same format it used to be in which mean i have
to check now which one of the fields now has additional upsale items
i.e added QTY and PartNo.

Any ideas on how i can work this?

Example
1. Raw input file looks like

MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc
S 1 3009

2. After my modification it looks like

MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc 1 3009

3. Output needs to look like raw data again

Any help will be apprecaited

thanks Neil

[MGFoster]

Neil wrote:

Hi guyz, just trying out this google feature so if i post if in the
wrong area i appologize. now to my question. BTW i'm new to access
programming, i've done a little vb6.0 and vb.net but access vba is a
little confusing for me since i have never used it plus i have lost
touch with vb coding since last 8 monhts.

anywayz, my problem is i'm trying to export a table that's modified by
me which is taken from a file at a prompt, now i have to export the
same file in the same format it came in Fixed and Delimited formats. I
can do this easily using the DoCmd.TransferText acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.txt" . All i do is change
the acExport to Fixed.

The file being imported has fixed width properties. It has 2 types of
records M(main set of record i.e Cust name, ph, email etc) and S(if the
operator upsold items it adds these records w/ corresponding field).
Once it is modified it now has just main record all M's. Now exporting
this format is easy but due to constraints i have to have the new
database output to the same format it used to be in which mean i have
to check now which one of the fields now has additional upsale items
i.e added QTY and PartNo.

Any ideas on how i can work this?

Example
1. Raw input file looks like

MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc
S 1 3009

2. After my modification it looks like

MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc 1 3009

3. Output needs to look like raw data again

Why don't you use the same import/export specification to export the
query/table data to a text file?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Neil]

> Why don't you use the same import/export specification to export the

query/table data to a text file?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Well it would be easier if my import were the same specification as
export. See when i import it does not have the extra fields that add
once i manipulate the file. That's the whole point i'm importing it so
i can read the file and make it the way i want it. I cannot add those
fields in advance coz unfortunately the data file comes in such way and
the reason we export the same way is coz its gonna go to a diff place
where it may be read the same way or not. That system is setup to
accept the same way our systems are. So unless we change all the
systems which could be expensive or time consuming, i have to import it
the way it is change it keep that Table and export it the way it came
in.

[Neil]

This is a sample code my cousin helped me with, can someone help me
fill the rest!

Function TextExport_Fixed()
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then

Else

End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next

strFix = strFix & vbCrLf

rs.MoveNext
Loop
rs.Close
Call WriteToFile
End Function

[Neil]

Can anyone help me finish the rest of this code my cousin help me put
together

Function TextExport_Fixed(Content As String, Filepath As String)
'DoCmd.TransferText acExportFixed, "LIVEOPS ORDER FIXED Export
Specification", "OVEN0219", "C:\Test2.txt"
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then
Content =
Else

End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next

strFix = strFix & vbCrLf

rs.MoveNext
Loop
rs.Close

Call WriteToFile
End Function
Public Function WriteToFile(Content As String, Filepath As String,
Optional Append As Boolean = False) _
As Boolean
'If Append = true, then content will be appended to existing file
contents
'else existing file is overwritten
'Returns: True if Successful, false otherwise
Dim iFile As Integer

iFile = FreeFile
If Append Then
Open Filepath For Append As #iFile
Else
Open Filepath For Output As #iFile
End If

Print #iFile, Content
SaveToFile = True

ErrorHandler:
Close #iFile
End Function

[MGFoster]

Neil wrote:

Hi guyz, just trying out this google feature so if i post if in the
wrong area i appologize. now to my question. BTW i'm new to access
programming, i've done a little vb6.0 and vb.net but access vba is a
little confusing for me since i have never used it plus i have lost
touch with vb coding since last 8 monhts.

anywayz, my problem is i'm trying to export a table that's modified by
me which is taken from a file at a prompt, now i have to export the
same file in the same format it came in Fixed and Delimited formats. I
can do this easily using the DoCmd.TransferText acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.txt" . All i do is change
the acExport to Fixed.

The file being imported has fixed width properties. It has 2 types of
records M(main set of record i.e Cust name, ph, email etc) and S(if the
operator upsold items it adds these records w/ corresponding field).
Once it is modified it now has just main record all M's. Now exporting
this format is easy but due to constraints i have to have the new
database output to the same format it used to be in which mean i have
to check now which one of the fields now has additional upsale items
i.e added QTY and PartNo.

Any ideas on how i can work this?

Example
1. Raw input file looks like

MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc
S 1 3009

2. After my modification it looks like

MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc 1 3009

3. Output needs to look like raw data again

Why don't you use the same import/export specification to export the
query/table data to a text file?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Neil]

> Why don't you use the same import/export specification to export the

query/table data to a text file?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Well it would be easier if my import were the same specification as
export. See when i import it does not have the extra fields that add
once i manipulate the file. That's the whole point i'm importing it so
i can read the file and make it the way i want it. I cannot add those
fields in advance coz unfortunately the data file comes in such way and
the reason we export the same way is coz its gonna go to a diff place
where it may be read the same way or not. That system is setup to
accept the same way our systems are. So unless we change all the
systems which could be expensive or time consuming, i have to import it
the way it is change it keep that Table and export it the way it came
in.

[Neil]

This is a sample code my cousin helped me with, can someone help me
fill the rest!

Function TextExport_Fixed()
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then

Else

End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next

strFix = strFix & vbCrLf

rs.MoveNext
Loop
rs.Close
Call WriteToFile
End Function

[Neil]

Can anyone help me finish the rest of this code my cousin help me put
together

Function TextExport_Fixed(Content As String, Filepath As String)
'DoCmd.TransferText acExportFixed, "LIVEOPS ORDER FIXED Export
Specification", "OVEN0219", "C:\Test2.txt"
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenRecordset(tblname)

Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If Left(rs.Fields(i).Name, 3) = "QTY" Then
Content =
Else

End If
strPad = Space(rs.Fields(i).FieldSize - Len(rs(i)))
strFix = strFix & rs(i) & strPad
Next

strFix = strFix & vbCrLf

rs.MoveNext
Loop
rs.Close

Call WriteToFile
End Function
Public Function WriteToFile(Content As String, Filepath As String,
Optional Append As Boolean = False) _
As Boolean
'If Append = true, then content will be appended to existing file
contents
'else existing file is overwritten
'Returns: True if Successful, false otherwise
Dim iFile As Integer

iFile = FreeFile
If Append Then
Open Filepath For Append As #iFile
Else
Open Filepath For Output As #iFile
End If

Print #iFile, Content
SaveToFile = True

ErrorHandler:
Close #iFile
End Function

[Neil]

Can anyon tell me why i am getting a Run-Time Error 3259: Invalid Field
Data Type for the following line:

strPad = Space(rs.Fields(i).FieldSize) - Len(rs(i))

All i'm trying to do is find the padding i need for the fixed width,
and add it to the string when i output the string to my file.

[pietlinden]

strPad = Space(rs.Fields(i).FieldSize) - Len(rs(i))

Parens are wrong.
strPad = Space(rs.fields(i).fieldsize - Len(rs(i))

This awful thing worked...

Public Sub WriteToFixedWidth(ByVal strTable As String)
Dim db As DAO.database
Dim tdf As DAO.tabledef
Dim rs As DAO.Recordset
Dim intCounter As Integer
Dim strTemp As String

Set db = CurrentDb
Set tdf = db.TableDefs(strTable)

Set rs = db.OpenRecordset(strTable, dbOpenForwardOnly)
Do Until rs.EOF
For intCounter = 0 To rs.Fields.Count - 1
strTemp = strTemp & rs.Fields(intCounter).Value &
Space(rs.Fields(intCounter).Size - Len(rs.Fields(intCounter).Value)) &
"|"
If intCounter = rs.Fields.Count - 1 Then
'--NOTE: Instead of writing to the screen here,
'--------use Write to add to your open file.
Debug.Print strTemp
strTemp = ""
End If
Next intCounter
'Debug.Print strTemp

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub