Exporting to multiple CSV files by state 1

[pazgb]

Hello,

I am having some trouble.

I am trying to export a table into a .CSV format(docmd.transfertext method).

But...

Each .CSV file needs to be by State. I have a table(tblImportToAGT) with the following fields:

Example of table data
ST SSN LASTNAME
AK 111111111 BRUCKMAN
AK 222222222 DOW
MI 333333333 LEVINSON
MN 444444444 REED
MN 555555555 URLING

I would get 3 different state .CSV files AR, MI, and MN.

Could someone put an example of some code that could be done to do this?

Thank You in advance!
Mike

 

[snyperx3]

why not make three queries, and use docmd.transfertext thrice and specify the query name as the "table name"


-Pete

 

[pazgb]

I wish it was that easy, but I do not know how many states(Max of 52) there will be in the table. I do not want to create 10 empty state files if no record exists for that state.

Is there another option?

 

[snyperx3]

I apologize. I misunderstood your original post to mean that you would ONLY EVER get 3 different states. I guess right now youre just testing with the three states in there. I don't immediately know how to solve this problem, but I'm looking. =]

-Pete

 

[snyperx3]

are the fields in the example the only fields in the table that you want to export?
what is the table name?
do you want the files named "ak.csv", "mi.csv" and so on?


-Pete

 

[snyperx3]

This is something i whipped up with the information you provided. check the names of the fields i used and such.

Dim dbs as DAO.Database
Dim rstStates,rstOutput as DAO.RecordSet
Dim FileHandle as Integer

Set dbs = CurrentDb()
Set rstStates = dbs.OpenRecordset("SELECT DISTINCT ST FROM tblData;",dbOpenDynaset)

Do until rst.EOF
     FileHandle = FreeFile
     Open rstStates![ST] & ".csv" For Output as #FileHandle

     Set rstOutput = dbs.OpenRecordset("SELECT * FROM tblData WHERE ST = '" & rstStates![ST] & "';",dbOpenDynaset)
     Do until rst.EOF
          Output #FileHandle, rstOutput![ST], rstOutput![SSN], rstOutput![LASTNAME]
     Loop
     Close #FileHandle
Loop

Make sure you have a reference set to the Microsoft DAO Library.

-Pete

 

[snyperx3]

And of course i forgot the MoveNext statements. Im sorry.

Dim dbs as DAO.Database
Dim rstStates,rstOutput as DAO.RecordSet
Dim FileHandle as Integer

Set dbs = CurrentDb()
Set rstStates = dbs.OpenRecordset("SELECT DISTINCT ST FROM tblData;",dbOpenDynaset)

Do until rstStates.EOF
     FileHandle = FreeFile
     Open rstStates![ST] & ".csv" For Output as #FileHandle

     Set rstOutput = dbs.OpenRecordset("SELECT * FROM tblData WHERE ST = '" & rstStates![ST] & "';",dbOpenDynaset)
     Do until rstOutput.EOF
          Output #FileHandle, rstOutput![ST], rstOutput![SSN], rstOutput![LASTNAME]
          rstOutput.MoveNext
     Loop
     Close #FileHandle
     rstStates.MoveNext
Loop

-Pete

 

[pazgb]

Thank you for your help.

I am getting a syntax error on this line:

Output #FileHandle, rstOutput![ST], rstOutput![SSN], rstOutput![LASTNAME]

I added the DAO 3.6 Object Libaray

Any Ideas?

Thanks,
Mike

 

[snyperx3]

Replace "Output" with "Write"


-Pete

 

[pazgb]

Works perfectly! Thanks alot!

 

[pazgb]

The CSV files all output data but I need them to output as a text datatype. Right now when I open the CSV file in EXCEL the datatype is GENRAL. Is there a way when I am exporting to CSV to make sure that the datatype is text.

The reason is, I have to then take these files and upload them as a text csv file.

I made sure that the table that is being exported is a text field.

Please see the code below

Anyone have any ideas?


Private Sub cmdSplit_Click()
'************************************************************************************************************
'Event Name: cmdSplit_Click()
'Purpose: Process will import OLQ file user selected in cmdBrowse_Click() and export data into
' individual files by state with the required fields. Also populate filename in listbox.
'Created Date: 02-01-2006
'Last Updated: NA
'************************************************************************************************************
'On Error GoTo Err_cmdSplit_Click
'Warnings Off
DoCmd.SetWarnings False
'Make sure user selected an import file during browse mode
txtImportFile.SetFocus
If txtImportFile.Text <> "" Then

'Show status window
DoCmd.OpenForm "frmStatus", acNormal

'Declare for importing tables
Dim ShtName As String
Dim objSrc As Object
Dim X As Long
Dim tblName As String

'Declare for splitting files
Dim dbs As DAO.Database
Dim rstStates, rstOutput As DAO.Recordset
Dim FileHandle As Integer

Set dbs = CurrentDb()

'********************************************************************************************************
'IMPORT OLQ FILE AND RUN QUERIES
'********************************************************************************************************

DoCmd.RunSQL "DELETE * FROM tbl_OLQ_IMPORT;"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_OLQ_IMPORT", txtImportFile.Text
'Update Status window
[Forms]![frmStatus]![lblImportingDone].Visible = True

'Update Status window
[Forms]![frmStatus]![lblPulling].Visible = True
DoCmd.OpenQuery "qry_OLQ_AGTS", acViewNormal
DoCmd.OpenQuery "qry_OLQ_CORP", acViewNormal
'Update Status window
[Forms]![frmStatus]![lblPullingDone].Visible = True

'********************************************************************************************************
'AGENTS
'********************************************************************************************************
'Update Status window
[Forms]![frmStatus]![lblSplitting].Visible = True

'Split files
Set rstStates = dbs.OpenRecordset("SELECT DISTINCT STATE FROM tbl_OLQ_AGTS;", dbOpenDynaset)

'Empty Listbox on main form
lstFiles.SetFocus
For i = 0 To lstFiles.ListCount - 1
If i > lstFiles.ListCount - 1 Then
lstFiles.RemoveItem (i)
lstFiles.Requery
End If
Next i

'Create folder for CSV files
strPath = Dir("L:\Finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\", vbDirectory)
If strPath <> "" Then
' It Exists
strPath = "L:\finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\"
Else
' Does not Exist
MkDir ("L:\Finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd")) & "\"
strPath = "L:\finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\"
End If

'Split files
Do Until rstStates.EOF
If rstStates![State] <> "" Then
FileHandle = FreeFile
Open strPath & Format(Date, "yyyymmdd") & "_AGT_" & rstStates![State] & ".csv" For Output As #FileHandle

Set rstOutput = dbs.OpenRecordset("SELECT * FROM tbl_OLQ_AGTS WHERE STATE = '" & rstStates![State] & "';", dbOpenDynaset)
'If ShtName = "CORP" Then
Do Until rstOutput.EOF
Write #FileHandle, rstOutput![SSN], rstOutput![Name]
rstOutput.MoveNext
Loop
lstFiles.AddItem strPath & Format(Date, "yyyymmdd") & "_AGT_" & rstStates![State] & ".csv"
Close #FileHandle
End If
rstStates.MoveNext
Loop

'********************************************************************************************************
'CORP
'********************************************************************************************************
'Get all states from OLQ file
Set rstStates = dbs.OpenRecordset("SELECT DISTINCT STATE FROM tbl_OLQ_CORP;", dbOpenDynaset)

'Create folder for CSV files
strPath = Dir("L:\Finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\CORP\", vbDirectory)
If strPath <> "" Then
' It Exists
strPath = "L:\finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\CORP\"
Else
' Does not Exist
MkDir ("L:\Finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\CORP\")
strPath = "L:\finance\AMAccessApplications\ExpiredLicUpload\SplitOLQ\" & Format(Date, "yyyymmdd") & "\CORP\"
End If

'Split files
Do Until rstStates.EOF
If rstStates![State] <> "" Then
FileHandle = FreeFile
Open strPath & Format(Date, "yyyymmdd") & "_CORP_" & rstStates![State] & ".csv" For Output As #FileHandle

Set rstOutput = dbs.OpenRecordset("SELECT * FROM tbl_OLQ_CORP WHERE STATE = '" & rstStates![State] & "';", dbOpenDynaset)
'If ShtName = "CORP" Then
Do Until rstOutput.EOF
Write #FileHandle, rstOutput![SSN]
rstOutput.MoveNext
Loop
lstFiles.AddItem strPath & Format(Date, "yyyymmdd") & "_CORP_" & rstStates![State] & ".csv"
Close #FileHandle
End If
rstStates.MoveNext
Loop
'Update Status window
[Forms]![frmStatus]![lblSplittingDone].Visible = True
[Forms]![frmStatus]![cmdClose].Enabled = True
[Forms]![frmStatus].Caption = "Process Successful!"

Else
'Error Message to User
MsgBox "You must browse for an import file.", vbCritical, "Error..."

End If
'Warnings On
DoCmd.SetWarnings True

'Exit_cmdSplit_Click:
' Exit Sub

'Err_cmdSplit_Click:
' MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description
' Resume Exit_cmdSplit_Click

End Sub

 

[ddiamond]

I assume your problem is the field SSN. When you look at the CSV file, do you see quotation marks around it?

 

[snyperx3]

you need to upload them where? If you need them just to be a text file then change

Open strPath & Format(Date, "yyyymmdd") & "_CORP_" & rstStates![State] & ".csv" For Output As #FileHandle

to

Open strPath & Format(Date, "yyyymmdd") & "_CORP_" & rstStates![State] & ".[red]txt[/red]" For Output As #FileHandle

It doesn't change the format of the file whatsoever, it just changes the programs that windows associates with it. I don't understand what you are doing that requires you to change the format of the file. sorry. =\

-Pete

 

[pazgb]

ddimond:

The CSV does not have any quotation marks.

snyperx3:

The file I need to submit to a batch process requires a CSV file.

Company Specialized Report (Batch Request)


The Specialized Report allows you to select from a list of PDB Data Fields to create a customized report. Producers may be typed in or submitted in Comma-Delimited (CSV) files. No charges will be incurred until the report is accepted with the knowledge of how many lines it contains and the total cost of the report.

 

[snyperx3]

So if ddiamond is right on the quotations marks, you can change

Write #FileHandle, rstOutput![SSN]

to

Write #FileHandle, CStr(rstOutput![SSN])

-Pete

 

[pazgb]

snyperx3,

The CStr() function did not work? Any other ideas?

 

[snyperx3]

CStr didnt work? hmmm. I guess you could use:

Write #FileHandle, """ & rstOutput![SSN] & """

-Pete

 

[pazgb]

Snyperx3:

I tried Write #FileHandle, """ & rstOutput![SSN] & """

Does not work either. Maybe we could try a different exporting methoed? Is there any other Access commands to export into a csv file?

 

[stix4t2]

You could use the docmd.transfertext command. Create one query to identify the states and then spin through that recordset to make a query that selects records. Then use the transfertext command to create the .csv files.

 

[PHV]

And this ?
Write #FileHandle, Chr(34) & rstOutput![SSN] & Chr(34)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886