Coding for exporting data into a text file format

[austin22]

I am using Access 2007 and I am trying to develop a small application for tax purposes. What I need to know is what is the best approach to exporting or converting the data in Access into a text file to be sent to recipient in the correct format. Here is what I have now...but this is hardcoded in Crystal Reports:

00000000TX09181964309612312008011620091000004277601NNN000000000CIY0069NNXXXX 
00000000TX09181964310312312008011620091000004277602NNN000000000CIY0163NNXXXX 
00000000TX09181964366812312008011620091000004277603NNN000000000CIY0043NNXXXX 
16232986TX06281985366801212007010620091000002447681NNN000000000CIY0200NNXXXX 
26448095TX04301989300107302008010720091000003947751NNN000000000CIY0200NNXXXX

I am a novice in this area, therefore, I am not sure how to approach this. Is there a code in Access or VB.net that can be used to extract the data that I need into a text file format?

Any help you can give will be greatly appreciated.

Thanks,

austin22

 

[PWise]

in what format do you have to sent it

 

[austin22]

It has to be sent in text format or ASCII format in Notepad.

 

[djj55]

Yes you can use Access or VB.NET to export a structured text file. Depending on your underlying data you may need to use a padding scheme to fill the zeros.



djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[austin22]

Will you send me an example on how to do so? I have no idea at this point.

 

[PHV]

You may export a formatting query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[austin22]

How?...Would it be like I have it in Crystal Reports? I did a query in CR and formatted the data as requested by the recipient. This was done because the formatted data that came with an already existing application stopped working.

I guess I do not understand the concept.

 

[austin22]

Okay when I export the data into Notepad, I get a bunch of garbage...Commas, spaces...etc. It does not look like the format I sent to you earlier. See first attachment.

 

[bradoirs]

Have you tried looping through the data, creating a string and then using open and print to write to a text file - I use this to prepare a bulk update for an online SQL/PHP database. Bit clumsy to set up and probably offends the professional programmers but works great



Do Until rst.EOF
webtext = "$sql = "
webtext = webtext & """"
webtext = webtext & "INSERT INTO `bsppix` ( `bspid`,`bspref` , `pic1`,`pic2` , `pic3` , `pic4`, `pic5`, `pic6`)"
webtext = webtext & "VALUES ('" & Trim(Str(rst!thoid)) & "', '"
webtext = webtext & rst!sunRef & "', '" & rst!php1 & "', '" & rst!php2 & "', '" & rst!php3 & "', '" & rst!php4 & "', '" & rst!php5 & "', '" & rst!php6 & "')"
webtext = webtext & """ "
webtext = webtext & ";" & vbCrLf
webtext = webtext & "$result = mysql_query($sql);" & vbCrLf
webtext = webtext & "if(mysql_affected_rows()>=1){" & vbCrLf
webtext = webtext & "$countokay=$countokay+1;" & vbCrLf
webtext = webtext & "echo $countokay;" & vbCrLf
webtext = webtext & "echo ') " & rst!thoid & ", " & rst!sunRef & "Has been added or amended <br>';" & vbCrLf
webtext = webtext & "}else{" & vbCrLf
webtext = webtext & "echo '<B>WARNING WARNING" & rst!thoid & "Has NOT BEEN added or amended </B><br>';" & vbCrLf
webtext = webtext & "}" & vbCrLf
webtext = replace(webtext, "c:\doctemp\bsp\", "")

Open "c:\publish\sunupload.php" For Append As #1 ' Open file for input.
Print #1, webtext
Close #1
rst.MoveNext
Loop

etc etc

 

[CautionMP]

when I export the data into Notepad, I get a bunch of garbage...Commas, spaces...etc.

Sounds like you selected Delimited in the Export Text Wizard. To replicate your report you will need to do Fixed Width.

As PHV eluded to you will need to build a query that formats each field the way it appears in the file (leading zeros, decimal offset...) then when you export the query as Fixed Width you can use the wizard to remove all the spaces between fields.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)