export to CSV without blank fields (1 Viewer)

namliam · Jun 17, 2015

Chintsapete said:
Thanks a lot, I didn't see that at all and the error message is/was rather confusing the matter than helping.
I must say progress is slow, but there is progress.
Your a star, thanks again

Loop without do, basicaly says you are missing "SOME" End, can be one of any many like Loop, Do, Next, End if, End With, etc...
Sloppy error message true, but once you know, it isnt hard to remember and spot the problem(s), particular if you keep PROPER formatting of your code...

Chintsapete · Jun 17, 2015

Hi Mailman
essentially I appended the fields needed into one table called EMP501, which is basically in the correct format except the issue with the blank fields for export. That is how I started and thought I can then just export to csv. Which of course didn't work as said earlier in the post.
What I did after was query the data from above table into the segments needed. There are 3 segments to the file, Company details, employee details and the totals. Company details start with code 2010, employee details start with code 3010 and the totals with code 6010 and every line ends with a code 9999. The employee details needs to the header of the file followed by all the employee records and the totals at end 1 line, which they use to verify the file. The totals query consists of the number of records and total all codes used and the sum of all payments which I calculate from the table emp501.
The idea was using your sub code to run in sequence Company, employees and totals to the export file.
I've been looking at loads of examples and tutorials with loops but I don't seem to be able to get it into my bonehead and maybe I'm to stuck on the original idea. I did learn a lot of other useful stuff along the way though but don't seem to get any closer to my goal.
I attached a sample New21.txt what should come out of it and the database table with the modules. Somehow the employee part is not looping correctly but I don't get why yet. It seems, as the code stands, that it restarts at the wrong spot and duplicates the employees instead of running once.
I know I ask a lot and I do understand if you don't have the time to have a look at it. I will have a look at the ParramArray.
Thanks a lot for all your help, I don't know how you do it but I sure appreciate it.
Pete

namliam · Jun 17, 2015

Really helps a lot if you describe what to do and see where it breaks...

Looks like sbtestExport works per your text file

Your Test1 (testemp) breaks only because you are not removing your line from memory after writing it
Try adding: MyLine = "" after the Debug.print

The Totals seems to be missing C2, but otherwize seems to do the job

Hope that helps you ?!

Chintsapete · Jun 17, 2015

So simple, yet I'm miles away. I would never have figured that one. Yep that seems to just work as it should.
The c2 is indeed missing that got somehow lost in the copy paste process reducing the base so I could upload it.
I'll try to run the 3 in sequence and x fingers I get it going.

Thanks a million, very amazing of you.

Chintsapete · Jun 18, 2015

YipYipYeeeeeeehaaaaaaaaa it works

Thanks a lot wow, so over the moon. Thank you.

I have one more problem I'm not sure about. Can I format numbers when they are in the VBA memory? Below 2 codes didn't work. So I'm not sure.

Thanks again for everything, couldn't have done it without you.

Code:

If Not IsNull(rs1!uifded) Then
            myLine = myLine & rs1!uifded & FormatNumber(rs1!uifded, 2) & ","
        End If

Code:

If Not IsNull(rs1!uifded) Then
            myLine = myLine & FormatNumber(rs1!uifded, 2) & ","
        End If

Working code below.

Code:

Option Compare Database



Sub sbExportAll()
    ' create your own csv
        
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim myLine As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("QEMP501Comp")
    Set rs1 = db.OpenRecordset("QEMP501Emp")
    Set rs2 = db.OpenRecordset("QEMP501Totals")
    Open "C:\Users\Server\Documents\Accounting\Test\Emp501.csv" For Output As #1
       Do While Not rs.EOF
       
        If Not IsNull(rs!C1) Then
            myLine = myLine & rs!C1 & ","
        End If
        If Not IsNull(rs!CompanyName_CertNo) Then
            myLine = myLine & rs!CompanyName_CertNo & ","
        End If
        If Not IsNull(rs!Test_CertType) Then
            myLine = myLine & rs!Test_CertType & ","
        End If
        If Not IsNull(rs!C3) Then
            myLine = myLine & rs!C3 & ","
        End If
        If Not IsNull(rs!PAYENo_Nature) Then
            myLine = myLine & rs!PAYENo_Nature & ","
        End If
        If Not IsNull(rs!C4) Then
            myLine = myLine & rs!C4 & ","
        End If
        If Not IsNull(rs!SDLNo_TaxYE) Then
            myLine = myLine & rs!SDLNo_TaxYE & ","
        End If
        If Not IsNull(rs!C5) Then
            myLine = myLine & rs!C5 & ","
        End If
        If Not IsNull(rs!UIFNo_Name) Then
            myLine = myLine & rs!UIFNo_Name & ","
        End If
        If Not IsNull(rs!C6) Then
            myLine = myLine & rs!C6 & ","
        End If
        If Not IsNull(rs!ContactPerson_FirstNames) Then
            myLine = myLine & rs!ContactPerson_FirstNames & ","
        End If
        If Not IsNull(rs!C7) Then
            myLine = myLine & rs!C7 & ","
        End If
        If Not IsNull(rs!PhoneContact_Initials) Then
            myLine = myLine & rs!PhoneContact_Initials & ","
        End If
        If Not IsNull(rs!C8) Then
            myLine = myLine & rs!C8 & ","
        End If
        If Not IsNull(rs!EmailContact_ID) Then
            myLine = myLine & rs!EmailContact_ID & ","
        End If
        If Not IsNull(rs!C9) Then
            myLine = myLine & rs!C9 & ","
        End If
        If Not IsNull(rs!Payroll_Passport) Then
            myLine = myLine & rs!Payroll_Passport & ","
        End If
        If Not IsNull(rs!C10) Then
            myLine = myLine & rs!C10 & ","
        End If
        If Not IsNull(rs!TrYear_CountryPP) Then
            myLine = myLine & rs!TrYear_CountryPP & ","
        End If
        If Not IsNull(rs!C11) Then
            myLine = myLine & rs!C11 & ","
        End If
        If Not IsNull(rs!Period_Bdate) Then
            myLine = myLine & rs!Period_Bdate & ","
        End If
        If Not IsNull(rs!C12) Then
            myLine = myLine & rs!C12 & ","
        End If
        If Not IsNull(rs!SIC7_TaxNo) Then
            myLine = myLine & rs!SIC7_TaxNo & ","
        End If
        If Not IsNull(rs!C13) Then
            myLine = myLine & rs!C13 & ","
        End If
        If Not IsNull(rs!SEZ_Sic7Emp) Then
            myLine = myLine & rs!SEZ_Sic7Emp & ","
        End If
        If Not IsNull(rs!C14) Then
            myLine = myLine & rs!C14 & ","
        End If
        If Not IsNull(rs!TradeClass_SEZEmp) Then
            myLine = myLine & rs!TradeClass_SEZEmp & ","
        End If
        If Not IsNull(rs!C15) Then
            myLine = myLine & rs!C15 & ","
        End If
        If Not IsNull(rs!AddressCO_EmailEmp) Then
            myLine = myLine & rs!AddressCO_EmailEmp & ","
        End If
        If Not IsNull(rs!C16) Then
            myLine = myLine & rs!C16 & ","
        End If
        If Not IsNull(rs!PHAddressCity_PHBusEmp) Then
            myLine = myLine & rs!PHAddressCity_PHBusEmp & ","
        End If
        If Not IsNull(rs!C17) Then
            myLine = myLine & rs!C17 & ","
        End If
        If Not IsNull(rs!ZIPCO_CellEmp) Then
            myLine = myLine & rs!ZIPCO_CellEmp & ","
        End If
        If Not IsNull(rs!C18) Then
            myLine = myLine & rs!C18 & ","
        End If
        
        
        
        ' Remove the last (useless comma)
        myLine = Left(myLine, Len(myLine) - 1)
        Write #1, myLine ' Or was it print #1, Myline ???
        rs.MoveNext
        myLine = ""
    Loop
    

    Do While Not rs1.EOF
    
        If Not IsNull(rs1!C1) Then
            myLine = myLine & rs1!C1 & ","
        End If
        If Not IsNull(rs1!CompanyName_CertNo) Then
            myLine = myLine & rs1!CompanyName_CertNo & ","
        End If
        If Not IsNull(rs1!Test_CertType) Then
            myLine = myLine & rs1!Test_CertType & ","
        End If
        If Not IsNull(rs1!C3) Then
            myLine = myLine & rs1!C3 & ","
        End If
        If Not IsNull(rs1!PAYENo_Nature) Then
            myLine = myLine & rs1!PAYENo_Nature & ","
        End If
        If Not IsNull(rs1!C4) Then
            myLine = myLine & rs1!C4 & ","
        End If
        If Not IsNull(rs1!SDLNo_TaxYE) Then
            myLine = myLine & rs1!SDLNo_TaxYE & ","
        End If
        If Not IsNull(rs1!C5) Then
            myLine = myLine & rs1!C5 & ","
        End If
        If Not IsNull(rs1!UIFNo_Name) Then
            myLine = myLine & rs1!UIFNo_Name & ","
        End If
        If Not IsNull(rs1!C6) Then
            myLine = myLine & rs1!C6 & ","
        End If
        If Not IsNull(rs1!ContactPerson_FirstNames) Then
            myLine = myLine & rs1!ContactPerson_FirstNames & ","
        End If
        If Not IsNull(rs1!C7) Then
            myLine = myLine & rs1!C7 & ","
        End If
        If Not IsNull(rs1!PhoneContact_Initials) Then
            myLine = myLine & rs1!PhoneContact_Initials & ","
        End If
        If Not IsNull(rs1!C8) Then
            myLine = myLine & rs1!C8 & ","
        End If
        If Not IsNull(rs1!EmailContact_ID) Then
            myLine = myLine & rs1!EmailContact_ID & ","
        End If
        If Not IsNull(rs1!C9) Then
            myLine = myLine & rs1!C9 & ","
        End If
        If Not IsNull(rs1!Payroll_Passport) Then
            myLine = myLine & rs1!Payroll_Passport & ","
        End If
        If Not IsNull(rs1!C10) Then
            myLine = myLine & rs1!C10 & ","
        End If
        If Not IsNull(rs1!TrYear_CountryPP) Then
            myLine = myLine & rs1!TrYear_CountryPP & ","
        End If
        If Not IsNull(rs1!C11) Then
            myLine = myLine & rs1!C11 & ","
        End If
        If Not IsNull(rs1!Period_Bdate) Then
            myLine = myLine & rs1!Period_Bdate & ","
        End If
        If Not IsNull(rs1!C12) Then
            myLine = myLine & rs1!C12 & ","
        End If
        If Not IsNull(rs1!SIC7_TaxNo) Then
            myLine = myLine & rs1!SIC7_TaxNo & ","
        End If
        If Not IsNull(rs1!C13) Then
            myLine = myLine & rs1!C13 & ","
        End If
        If Not IsNull(rs1!SEZ_Sic7Emp) Then
            myLine = myLine & rs1!SEZ_Sic7Emp & ","
        End If
        If Not IsNull(rs1!C14) Then
            myLine = myLine & rs1!C14 & ","
        End If
        If Not IsNull(rs1!TradeClass_SEZEmp) Then
            myLine = myLine & rs1!TradeClass_SEZEmp & ","
        End If
        If Not IsNull(rs1!C15) Then
            myLine = myLine & rs1!C15 & ","
        End If
        If Not IsNull(rs1!AddressCO_EmailEmp) Then
            myLine = myLine & rs1!AddressCO_EmailEmp & ","
        End If
        If Not IsNull(rs1!C16) Then
            myLine = myLine & rs1!C16 & ","
        End If
        If Not IsNull(rs1!PHAddressCity_PHBusEmp) Then
            myLine = myLine & rs1!PHAddressCity_PHBusEmp & ","
        End If
        If Not IsNull(rs1!C17) Then
            myLine = myLine & rs1!C17 & ","
        End If
        If Not IsNull(rs1!ZIPCO_CellEmp) Then
            myLine = myLine & rs1!ZIPCO_CellEmp & ","
        End If
        If Not IsNull(rs1!C18) Then
            myLine = myLine & rs1!C18 & ","
        End If
        If Not IsNull(rs1!PHWorkEmp) Then
            myLine = myLine & rs1!PHWorkEmp & ","
        End If
        If Not IsNull(rs1!C19) Then
            myLine = myLine & rs1!C19 & ","
        End If
        If Not IsNull(rs1!PHBusTownEmp) Then
            myLine = myLine & rs1!PHBusTownEmp & ","
        End If
        If Not IsNull(rs1!C20) Then
            myLine = myLine & rs1!C20 & ","
        End If
        If Not IsNull(rs1!PHZipEmp) Then
            myLine = myLine & rs1!PHZipEmp & ","
        End If
        If Not IsNull(rs1!C21) Then
            myLine = myLine & rs1!C21 & ","
        End If
        If Not IsNull(rs1!EmpNo) Then
            myLine = myLine & rs1!EmpNo & ","
        End If
        If Not IsNull(rs1!C22) Then
            myLine = myLine & rs1!C22 & ","
        End If
        If Not IsNull(rs1!AssPStart) Then
            myLine = myLine & rs1!AssPStart & ","
        End If
        If Not IsNull(rs1!C23) Then
            myLine = myLine & rs1!C23 & ","
        End If
        If Not IsNull(rs1!AssPEnd) Then
            myLine = myLine & rs1!AssPEnd & ","
        End If
        If Not IsNull(rs1!C24) Then
            myLine = myLine & rs1!C24 & ","
        End If
        If Not IsNull(rs1!PayPYear) Then
            myLine = myLine & rs1!PayPYear & ","
        End If
        If Not IsNull(rs1!C25) Then
            myLine = myLine & rs1!C25 & ","
        End If
        If Not IsNull(rs1!PayPWork) Then
            myLine = myLine & rs1!PayPWork & ","
        End If
        If Not IsNull(rs1!C26) Then
            myLine = myLine & rs1!C26 & ","
        End If
        If Not IsNull(rs1!EmpResAdd) Then
            myLine = myLine & rs1!EmpResAdd & ","
        End If
        If Not IsNull(rs1!C27) Then
            myLine = myLine & rs1!C27 & ","
        End If
        If Not IsNull(rs1!EmpResTown) Then
            myLine = myLine & rs1!EmpResTown & ","
        End If
        If Not IsNull(rs1!C28) Then
            myLine = myLine & rs1!C28 & ","
        End If
        If Not IsNull(rs1!EmpResZip) Then
            myLine = myLine & rs1!EmpResZip & ","
        End If
        If Not IsNull(rs1!C29) Then
            myLine = myLine & rs1!C29 & ","
        End If
        If Not IsNull(rs1!PostSameRes) Then
            myLine = myLine & rs1!PostSameRes & ","
        End If
        If Not IsNull(rs1!C30) Then
            myLine = myLine & rs1!C30 & ","
        End If
        If Not IsNull(rs1!StreetAddYN) Then
            myLine = myLine & rs1!StreetAddYN & ","
        End If
        If Not IsNull(rs1!C31) Then
            myLine = myLine & rs1!C31 & ","
        End If
        If Not IsNull(rs1!POBoxYN) Then
            myLine = myLine & rs1!POBoxYN & ","
        End If
        If Not IsNull(rs1!C32) Then
            myLine = myLine & rs1!C32 & ","
        End If
        If Not IsNull(rs1!POBagYN) Then
            myLine = myLine & rs1!POBagYN & ","
        End If
        If Not IsNull(rs1!C33) Then
            myLine = myLine & rs1!C33 & ","
        End If
        If Not IsNull(rs1!PostOffice) Then
            myLine = myLine & rs1!PostOffice & ","
        End If
        If Not IsNull(rs1!C34) Then
            myLine = myLine & rs1!C34 & ","
        End If
        If Not IsNull(rs1!PostZip) Then
            myLine = myLine & rs1!PostZip & ","
        End If
        If Not IsNull(rs1!C35) Then
            myLine = myLine & rs1!C35 & ","
        End If
        If Not IsNull(rs1!BoxBagNO) Then
            myLine = myLine & rs1!BoxBagNO & ","
        End If
        If Not IsNull(rs1!C36) Then
            myLine = myLine & rs1!C36 & ","
        End If
        If Not IsNull(rs1!BankAccType) Then
            myLine = myLine & rs1!BankAccType & ","
        End If
        If Not IsNull(rs1!c38) Then
            myLine = myLine & rs1!c38 & ","
        End If
        If Not IsNull(rs1!BankAccNo) Then
            myLine = myLine & rs1!BankAccNo & ","
        End If
        If Not IsNull(rs1!c39) Then
            myLine = myLine & rs1!c39 & ","
        End If
        If Not IsNull(rs1!BranchCode) Then
            myLine = myLine & rs1!BranchCode & ","
        End If
        If Not IsNull(rs1!c40) Then
            myLine = myLine & rs1!c40 & ","
        End If
        If Not IsNull(rs1!BankName) Then
            myLine = myLine & rs1!BankName & ","
        End If
        If Not IsNull(rs1!c41) Then
            myLine = myLine & rs1!c41 & ","
        End If
        If Not IsNull(rs1!BranchName) Then
            myLine = myLine & rs1!BranchName & ","
        End If
        If Not IsNull(rs1!c42) Then
            myLine = myLine & rs1!c42 & ","
        End If
        If Not IsNull(rs1!AccHolder) Then
            myLine = myLine & rs1!AccHolder & ","
        End If
        If Not IsNull(rs1!c43) Then
            myLine = myLine & rs1!c43 & ","
        End If
        If Not IsNull(rs1!AccRelation) Then
            myLine = myLine & rs1!AccRelation & ","
        End If
        If Not IsNull(rs1!c44) Then
            myLine = myLine & rs1!c44 & ","
        End If
        If Not IsNull(rs1!Income) Then
            myLine = myLine & rs1!Income & ","
        End If
        If Not IsNull(rs1!c45) Then
            myLine = myLine & rs1!c45 & ","
        End If
        If Not IsNull(rs1!Gross) Then
            myLine = myLine & rs1!Gross & ","
        End If
        If Not IsNull(rs1!c46) Then
            myLine = myLine & rs1!c46 & ","
        End If
        If Not IsNull(rs1!PensionDed) Then
            myLine = myLine & rs1!PensionDed & ","
        End If
        If Not IsNull(rs1!c47) Then
            myLine = myLine & rs1!c47 & ","
        End If
        If Not IsNull(rs1!TotalDed) Then
            myLine = myLine & rs1!TotalDed & ","
        End If
        If Not IsNull(rs1!c48) Then
            myLine = myLine & rs1!c48 & ","
        End If
        If Not IsNull(rs1!uifded) Then
            myLine = myLine & rs1!uifded & ","
        End If
        If Not IsNull(rs1!c49) Then
            myLine = myLine & rs1!c49 & ","
        End If
        If Not IsNull(rs1!SDL) Then
            myLine = myLine & rs1!SDL & ","
        End If
        If Not IsNull(rs1!c50) Then
            myLine = myLine & rs1!c50 & ","
        End If
        If Not IsNull(rs1!TotalDedCE) Then
            myLine = myLine & rs1!TotalDedCE & ","
        End If
        If Not IsNull(rs1!c51) Then
            myLine = myLine & rs1!c51 & ","
        End If
        If Not IsNull(rs1!reasoncode) Then
            myLine = myLine & rs1!reasoncode & ","
        End If
        If Not IsNull(rs1!c52) Then
            myLine = myLine & rs1!c52 & ","
        End If
        If Not IsNull(rs1!Paye) Then
            myLine = myLine & rs1!Paye & ","
        End If
        If Not IsNull(rs1!C78End) Then
            myLine = myLine & rs1!C78End & ","
        End If
        
        ' Remove the last (useless comma)
        myLine = Left(myLine, Len(myLine) - 1)
        Write #1, myLine ' Or was it print #1, Myline ???
        rs1.MoveNext
        myLine = ""
    Loop
    

    Do While Not rs2.EOF
    
        If Not IsNull(rs2!C1) Then
            myLine = myLine & rs2!C1 & ","
        End If
        If Not IsNull(rs2!CompanyName_CertNo) Then
            myLine = myLine & rs2!CompanyName_CertNo & ","
        End If
        If Not IsNull(rs2!C2) Then
            myLine = myLine & rs2!C2 & ","
        End If
        If Not IsNull(rs2!Test_CertType) Then
            myLine = myLine & rs2!Test_CertType & ","
        End If
        If Not IsNull(rs2!C3) Then
            myLine = myLine & rs2!C3 & ","
        End If
        If Not IsNull(rs2!PAYENo_Nature) Then
            myLine = myLine & rs2!PAYENo_Nature & ","
        End If
        If Not IsNull(rs2!C4) Then
            myLine = myLine & rs2!C4 & ","
        End If
        
        ' Remove the last (useless comma)
        myLine = Left(myLine, Len(myLine) - 1)
        Write #1, myLine ' Or was it print #1, Myline ???
        rs2.MoveNext
        myLine = ""
    Loop

    Close
End Sub

Chintsapete · Jun 19, 2015

I just want to correct myself for the last post. I did realize in the meantime it does work. I fell for checking in the VB editor in debug mode and there it showed still 4 digits after the decimal point, but the actual output is correct.

Code:
I have one more problem I'm not sure about. Can I format numbers when they are in the VBA memory? Below 2 codes didn't work. So I'm not sure.

Code:

If Not IsNull(rs1!uifded) Then myLine = myLine & rs1!uifded & FormatNumber(rs1!uifded, 2) & "," End If

namliam · Jun 19, 2015

myLine = myLine & rs1!uifded & FormatNumber(rs1!uifded, 2) & ","

Using the same field twice like this doesnt seem logical?

pbaldy · Jun 19, 2015

Chintsapete said:
I just want to correct myself for the last post. I did realize in the meantime it does work. I fell for checking in the VB editor in debug mode and there it showed still 4 digits after the decimal point, but the actual output is correct.

In case you didn't realize, when focus is on a line in debug mode, that line hasn't been executed yet. Think of the line in yellow as the line about to be executed, not the line just executed. Perhaps you were looking at it before it executed.

Chintsapete · Jun 20, 2015

In case you didn't realize, when focus is on a line in debug mode, that line hasn't been executed yet. Think of the line in yellow as the line about to be executed, not the line just executed. Perhaps you were looking at it before it executed.

Not sure about it but will pay attention in future, thanks.

myLine = myLine & rs1!uifded & FormatNumber(rs1!uifded, 2) & ","

Using the same field twice like this doesnt seem logical?

I've seen that version somewhere in google search and tried it out. But actually ended up using the below. Which worked to perfection.

Code:

If Not IsNull(rs1!uifded) Then
            myLine = myLine & Format(rs1!TotalDedCE, "####0.00") & ","
        End If

Thanks for all your help again. I cleaned up everything yesterday and now I can push the button and the file exports and imports.

. The only thing I have to do manually is deleting the " at the beginning and end of each line.
Awesome!!!!!!!!!!!

namliam · Jun 20, 2015

The " I think may be related to your use of the Write function, try using print instead.

Chintsapete · Jun 22, 2015

You genius it's indeed, print works without ". Perfection is beautiful

Thank you so much

export to CSV without blank fields (1 Viewer)

namliam

The Mailman - AWF VIP

Chintsapete

Registered User.

Attachments

namliam

The Mailman - AWF VIP

Chintsapete

Registered User.

Chintsapete

Registered User.

Chintsapete

Registered User.

namliam

The Mailman - AWF VIP

pbaldy

Wino Moderator

Chintsapete

Registered User.

namliam

The Mailman - AWF VIP

Chintsapete

Registered User.

Similar threads

Users who are viewing this thread