VBA Export Data to in Table and generate group .csv files (by defined criteria)

[Susy]

Hi there,



I am back with another challenge for me and for those who would like to help.

I need to come up with a vba code which reads the data from tbl_data and create separate .csv files as shown in the image attached.

Criteria for grouping:
1. group contractnumber by id, if not empty and use the first "personName" for all records in the group when createing .csv file
2. if contractnumber is empty then there is no grouping. Record is considered as one .csv file

After the grouping and "personName" assignment is done, for every group (either a group or just a record) a .csv need to be saved in in a folder by using "personName" (if personName in group is different, take the first name, see image above)

If you have an idea, code or suggestion, please let me know.

Regards!

[ranman256]

make a query for each csv you need. (with fields you need)
make a csv export specName. (manually export the query, click ADVANCED in bottom left corner, setup your spec, save)
make a macro to export each query...

docmd.TransferText acExportFixed ,specName,query,filename,true

[Susy]

Hi Ranman,

what do you mean with "create a query for each csv I need". I get thousands of cases...


Thanks in advance.

[ssanfu]

Can you provide examples of the CSV files? AkidoX.csv and JohnZ.csv

The code is not that hard, just have to know what the results/format should be....

[Susy]

Hi guys,

I came up with this by myself ...., really!

I still feel uncomfortable with putting two recordsets and only setting one to my currentdb...

Can anyone check my code, please, and give me some suggestions to make it better.... it is working!!!!... but it could be much better, I believe.

Did I forget something in the code?

Sub QryCSVCreation()

Dim mySecNrQry, myIntoQry, mysectionNr, mypersName, myfoldername As String
Dim myrs As Recordset




    mySecNrQry = "SELECT DISTINCT tbl_data.SectionNumber "
    mySecNrQry = mySecNrQry & "FROM tbl_data "
    mySecNrQry = mySecNrQry & "ORDER BY tbl_data.SectionNumber ASC;"




Set myrs = CurrentDb.OpenRecordset(mySecNrQry)


    myrs.MoveFirst
    
    
    Do While Not myrs.EOF
        mysectionNr = myrs!SectionNumber
        'mypersName = myrs!personName
        myrs.MoveNext
        
        
    myIntoQry = "SELECT tbl_data.* INTO tbl_Importdata"
    myIntoQry = myIntoQry & "FROM tbl_data "
    myIntoQry = myIntoQry & "WHERE tbl_data.SectionNumber = " & mysectionNr & ";"




    DoCmd.RunSQL myIntoQry


ChDir "C:\Users\Susan\Documents\AccessDB\CSV Folder\"
myfoldername = "C:\Users\Susan\Documents\AccessDB\CSV Folder\" & mysectionNr & ".csv"


    DoCmd.TransferText acExportDelim, "mySectionSpec", "tbl_Importdata", myfoldername, True, , 65001




    Loop    
    
End Sub

Regards!

[Susy]

I guess it is good enough!

Thanks

[ssanfu]

Patience weedjumper.........

[ssanfu]

Dim mySecNrQry, myIntoQry, mysectionNr, mypersName, myfoldername As String

This line doesn't do what you think it does - it declares "myfoldername" as a string and all of the other variables in the line as variants.
You must explicitly declare the variable type.

Dim myrs As Recordset

This should be "As DAO.Recordset

myIntoQry = "SELECT tbl_data.* INTO tbl_Importdata"  <snip>

This line constantly deletes, then recreates a table ("tbl_Importdata"). It would be better to create the table once, then delete and insert the records.
Creating tables "on-the-fly" can introduce corruption into your dB.

ChDir "C:\Users\Susan\Documents\AccessDB\CSV Folder\"

There is no need to change the current directory. And because it is inside a loop, that command is executing as many times as the code loops.


No code to clean up the object ("myrs") created. Should have two lines

myrs.Close
Set myrs = Nothing

Here is my example.....

[Susy]

Hi Steve,

thanks for your code. It is way out of my league... I am still googleing terms and methods... always learning.

First Question: How to programm like that? Books, tutorials? Another programming language?

Second Question:
"This line constantly deletes, then recreates a table ("tbl_Importdata"). It would be better to create the table once, then delete and insert the records.
Creating tables "on-the-fly" can introduce corruption into your dB."


After removing the setwarnings... I get the following message: "The existing table 'tbl_Importdata' will be deleted before you run the query..."

I just wanted to delete all records from the existing table and add new ones everytime... but as you said, it is deleting the table. Can you help me with that?

The reason why I need that is that the table I will actually use is around 124 columns long with various data types.... Using the "TransferText" command let me define the datatypes before exporting
and I don't need to write the headers.


Your code is awesome and I hope everyone lernt as well.


Hope to hear from you again. Thanks!

[ssanfu]

OK, try this.

This dB didn't have the export specification "mySectionSpec" so I couldn't do any testing unless I deleted it in the transfer text command.
But it seems to work .....

[Susy]

Hi Steve,

thanks again... this is what I had in mind...

Back to my question:
How to programm like that? Books, tutorials? Another programming language?

Regards!

[ssanfu]

How to programm like that? Books, tutorials? Another programming language?

I would have to say it is a combination of knowledge and experience. Once you know how to program in one language, it is easier to learn other languages.

I taught myself BASIC (acronym of B(eginner's) A(ll-purpose) S(ymbolic) I(nstruction) C(ode)) - IBM BASIC, Apple Integer BASIC and Apple Floating point BASIC (both on an Apple II+) and Turbo Pascal (was difficult for me because it is a bottom up structure - took a while to understand).
I can somewhat read Fortran, COBOL, C, C++, C# (but out of practice) and a few others.

Books are important; good reference books help with syntax and structure of the language. I have lots of reference books for different languages.
Tutorials are very good.
Examples of code that you can tear apart and analyze are another good thing. (I have done a lot of that)



Experience is: Just what it means. You have to do a lot of programming. Doesn't matter what you do, you have to get experience to be good. It goes for boxing, racing cars, golfing, painting, singing, etc......



So knowledge includes:
"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in [insert language here]... You have to (or should) understand what pseudo-code means.
This is what you what to do in general steps... kinda like at the 10,000 foot level.
You need to understand the syntax of the commands of the language.
You have to understand the logic of the algorithm, ie the steps involved/required. To rebuild a carburetor, there is a specific order to the steps (aka an algorithm). Coding is the same......



For your problem, the pseudo-code might have started out like: "I want to write specific records to different CSV files."

Next, start refining the problem:
Get the unique sectionNumber values
For each sectionNumber, get the records that match.
Write the records to a different table.
Export the records in the "different table" to a text file.

Then, include more detail.
Then start using the VBA commands, to write the code. Step through the code mentally to see if the steps are correct. Then execute the code, single stepping through the code checking variables to ensure the code is doing what you want in the correct order and logical.
This is where I saw the ChDir command inside the loop - the path wasn't changing, so why was the command inside the loop?? Follow the logic....

Lastly, go into detail....

[Susy]

Thanks Steve..

I will follow the rule "If you can't do it on paper then you can't do it in the computer!" and definitively work on pseudo-codes more.

Regards