473,903 Members | 5,741 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Export table to TXT file using 2 methods

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.TransferT ext acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.tx t" . 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

Nov 13 '05 #1
10 14747
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.TransferT ext acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.tx t" . 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Nov 13 '05 #2
> Why don't you use the same import/export specification to export the
query/table data to a text file?

--
MGFoster:::mgf0 0 <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.

Nov 13 '05 #3
This is a sample code my cousin helped me with, can someone help me
fill the rest!

Function TextExport_Fixe d()
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenR ecordset(tblnam e)

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

Nov 13 '05 #4
Can anyone help me finish the rest of this code my cousin help me put
together

Function TextExport_Fixe d(Content As String, Filepath As String)
'DoCmd.Transfer Text acExportFixed, "LIVEOPS ORDER FIXED Export
Specification", "OVEN0219", "C:\Test2.t xt"
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenR ecordset(tblnam e)

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(Con tent 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

Nov 13 '05 #5
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.TransferT ext acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.tx t" . 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Nov 13 '05 #6
> Why don't you use the same import/export specification to export the
query/table data to a text file?

--
MGFoster:::mgf0 0 <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.

Nov 13 '05 #7
This is a sample code my cousin helped me with, can someone help me
fill the rest!

Function TextExport_Fixe d()
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenR ecordset(tblnam e)

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

Nov 13 '05 #8
Can anyone help me finish the rest of this code my cousin help me put
together

Function TextExport_Fixe d(Content As String, Filepath As String)
'DoCmd.Transfer Text acExportFixed, "LIVEOPS ORDER FIXED Export
Specification", "OVEN0219", "C:\Test2.t xt"
Dim rs As DAO.Recordset
Dim strFix As String
Dim strSRecord As String
Dim strPad
Set rs = CurrentDb.OpenR ecordset(tblnam e)

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(Con tent 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

Nov 13 '05 #9
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.

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5049
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table. Before I iterate through the recordset I instruct the browser that the content type is Excel using the following line: (Response.ContentType = "application/vnd.ms-excel") This works fine with Excel 2003 but with older versions (I tested Excel...
0
443
by: Neil | last post by:
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...
3
3056
by: excyauseme | last post by:
Hi guys! Do you know what is the best way to export a text file, this one is a log file that is already comma delimited thru a module run by my access database, to an excel spreadsheet? I need to do this thru the module, and I don't see how I can export a txt file from access without loading it to a temp table first. I've checked out the DoCmd.TransferText methods, where you have to have a table or query or schema ini first, and the...
3
3964
by: Daniel Wetzler | last post by:
Dear MSSQL- experts, I have a strange problem with SQLSERVER 2000. I tried to export a table of about 40000 lines into a text file using the Enterprise manager export assitant. I was astonished to get an exported text file of about 400 MB instead 16 MB which is the normal size of that data. By examining this file with a text editor I found that the file included alongside the data of my table MANY zeros which caused the big file size.
17
7266
by: Fabry | last post by:
Hi All, I'm new of this group and I do not know if this is the correct group for my question. I have a DLL with its export library (.lib) wrote in Borland C++ 6. In borland everything is OK and I am able to include the lib and use the class that i have exported creating an instance with new etc... I would like to export this class in microsoft VC++ using the same .lib file. Obviously it doesn' t work.
9
7721
by: NEWSGROUPS | last post by:
I have data in a table in an Access 2000 database that needs to be exported to a formatted text file. For instance, the first field is an account number that is formatted in the table as text and is 8 characters long. This field needs to be exported as pic(15) padded in the front with 0's (zeros). The next field an ID name that is 15 characters that needs to be exported as pic(20) padded with trailing spaces. There are about 5 fields in...
5
16384
by: bhodgins | last post by:
Hi, I am new on here, and had a newbie question that I am stumped with. I am not new to access, but am new to VB. I am trying to export BLOBs from a field called photo to external jpeg files. I have tried the MS kb 210486 and successfully got the import/export to work with a sample table, but only the first record. I do not wish to impost binary data to the database, it's already there. I simply wish to pick up the binary BLOB from the photo...
3
4568
by: Edward Reid | last post by:
OK, I've been watching my DB grow and have known for a long time I would have to move the back end, but of course I waited until I started getting odd behavior (at about 1.92GB). So today I installed MySQL and (after a bit too much time getting the data source working) started exporting tables. I successfully exported eight of my nine tables, and can view them from a linked Access front end. But when I tried to export the final table,...
8
18849
by: iheartvba | last post by:
Hi I am using Access 2007 and am trying to export a query to a fixed length text file. I tried using the following code to export the text file: DoCmd.TransferText acExportFixed, , "qryFFRDeFile", "C:\qryFFRDeFile.txt", True Access asks for a specification name, now here lies the challenge, how do I create an export specification I have tried the following 2 methods: In access 2007: Step1: I right clicked the query and chose export to...
0
9849
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11285
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10878
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9688
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5894
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6094
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4727
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4308
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3324
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.