export to CSV without blank fields

C

Chintsapete

Registered User.
Local time
Tomorrow, 00:24
Joined
Jun 15, 2012
Messages
137
I have to import a table into a government database. I've got the data lined up in a table and exported to csv. The problem is that the government database doesn't allow empty fields, or the commas created by empty fields. The empty fields are create by criteria that changes with each employee. Is there a way to get rid of them without manually deleting them? I would imagine there is but can't find anything about it or maybe I don't see the forest for the trees? Sample csv below with surplus commas.
Thanks for any help.
Code: 
3010,702073771620150200000000000001,3015,IT3(a),3020,A,3025,2015,,,3030,Sabelo,3040,Nkosinathi ,3050,NE,3060,6012125391085,3070,,3075,,3080,19601212,3100,0000000000,3263,55103,3264,ZAR,3125,,3136,0437343012,3138,,3147,Farm 1317,3149,Vincent,3150,5275,3160,1,3170,20140801,3180,20140925,3200,52.0000,3210,7.0000,3214,PO Box 13092,3216,Vincent,3217,5217,3218,,3247,N,3249,X,3253,,3254,,3262,,3240,0,,,,,,,,,,,3601,37,3698,37,4001,146,4497,147,4141,3.70,4142,1.85,4149,5.55,4150,02,,,,,,9999
 
I would assume that the empty fields have to remain in place, as the import process would expect fields in certain positions. You should be able to create a query that uses the Nz() function to replace Null values with whatever (0, "None", etc).
 
Thanks for the reply.
The order doesn't matter to much within the file. The problem is I append the data to a table with a query and therefore the fields always append to the same field. I made conditions in the append query so fields not needed are Null, but that creates the spare commas. If I don't do the conditions the government database rejects the file. I could make a query per employee but that's not really practical in the long run.
 
Again, simplest would be to replace the Nulls with something:

Nz(FieldName, 0)
 
Nope, that's when the complaint comes in "Error-Wrong code", hence it shouldn't export, that is, make a double comma. It took me hours to figure out what the problem is and I'm not sure what the guys on the government side where thinking not allowing blank spaces but there is not much I can do about it. Manually cleaning up the file works, but very tedious.
Trim function for the whole line maybe? But not sure if that works, I never used it and my VBA coding is non existent except some bare bones idea.
I don't think Schema.ini can help either since it seems not to have a function to not map blank spaces. It's a bit of a nightmare really, to follow a 42 page instruction manual which is not super consistent.
 
Well you can easily either clean the text file with VBA or create your own export ...

Though I am with Paul, removing the blank fields would seem to lead to an inconsistant file and data... but... That is your call I guess....

Something along the lines of
Code: 
Sub x()
    ' create your own csv
    Dim rs As DAO.Recordset
    Dim myLine As String
    Set rs = CurrentDb.OpenRecordset("YourQuery")
    Open "C:\Temp\Yourfile.csv" For Output As #1
    Do While Not rs.EOF
        If Not IsNull(rs!Thisfield) Then
            myLine = myLine & rs!Thisfield & ","
        End If
        ' Dont need checks for these fields
        myLine = myLine & rs!Thatfield & "," & rs!MyField & ","
        If Not IsNull(rs!Somefield) Then
            myLine = myLine & rs!Somefield & ","
        End If
        
        ' Remove the last (useless comma)
        myLine = Left(myLine, Len(myLine) - 1)
        Write #1, myLine ' Or was it print #1, Myline ???
        rs.MoveNext
    Loop
End Sub

Cleaning the file after creation would simply be an read/write operation with a couple of Replace statements inbetween to clear our all ,, combinations.
 
Thanks Mailman, saving me again. I'll give it a go.
Stay happy :)
 
Hi Namliam
I finally got around to try it out. I made a module in VBA called "export" and copied your code into it and changed it as I thought fit. But how do I run it?
I tried to, in the macro at the end of the queries where I append all the data into a table, open the table and than RunCode -> Export. But it doesn't like that at all, "no automation object etc".
If you have a second to help would be awesome.
Thanks Pete
 
The postal worker appears to be offline. You could put that code behind a button, or if you want to call it from anywhere, make it a public function in a standard module. I don't think you can call a sub from a macro, only a function.
 
pbaldy said:
I don't think you can call a sub from a macro, only a function.
Click to expand...

Confirmed, also you cannot call a sub or function the same as your module.
Which is why it is good practice to adhere to a naming convention where you prefix your objects....
I.e.
mdlExport (for your export module)
sbExport (for the sub)
fnExport (if it were a funtion)
tblSomething (for a table called Something)
etc...

If you have queries to run you can do this from within the sub/module easily...
Code: 
Currentdb.Execute("YourQueryName")
will execute any append/delete query you want without messages, though duplicating data just for reporting or exporting purposes.... well duplicating data for any reason really is a significant NO NO...
At the very least it will cause bloating, at worst it can cause data inconsistancies or even data loss....
 
Thanks again to both of you for the quick response, mind you I had to knock off last night, my head was spinning.
I don't really duplicate the data, I just pull it and arrange it in a temp table for export. That whole story is to make tax certificates automatically on the tax man's DB which'll save hours of work every six months. Truly speaking this was so far a challenge of note and I am a bit over the top, but I got so far and I need to clean it up to make it work for the future without all the trouble. I had another shot at it with the explanations above and ended up with SQL problems again in the queries. So dear Mailman, I'm back to fnETI and your lessons there, which I of course completely neglected so far since I didn't have a chance to learn much more about it, but thinking now is the time to do so, because I ended up with below, which works except to auto clean up in the export and of course implementing your solution.
Many thanks again and I probably will have the odd question but I will focus on doing it as you suggested a long time ago.
Code: 
SELECT First(CompanyDetails.EasC01) AS FirstOfEasC01, CompanyDetails.CompanyName, First(2015) AS c2, "Live" AS Test, First(CompanyDetails.EasC02) AS FirstOfEasC02, CompanyDetails.PAYENo, First(CompanyDetails.EasC03) AS FirstOfEasC03, CompanyDetails.SDLNo, First(CompanyDetails.EasC04) AS FirstOfEasC04, CompanyDetails.UIFNo, First(CompanyDetails.EasC06) AS FirstOfEasC06, CompanyDetails.ContactPerson, First(CompanyDetails.EasC07) AS FirstOfEasC07, CompanyDetails.PhoneContact, First(CompanyDetails.EasC08) AS FirstOfEasC08, CompanyDetails.EmailContact, First(2028) AS c9, "In-House" AS Payroll, First(2030) AS c10, [Salaries YTD].financialyear AS TrYear, First(2031) AS c11, Format([Forms]![EMP501]![ToDate],"yyyymm") AS Period, First(CompanyDetails.EasC09) AS FirstOfEasC09, CompanyDetails.SIC7, First(CompanyDetails.EasC10) AS FirstOfEasC10, CompanyDetails.SEZ, First(CompanyDetails.EasC11) AS FirstOfEasC11, CompanyDetails.TradeClass, First(CompanyDetails.EasC12) AS FirstOfEasC12, CompanyDetails.PHAddressUnit, First(CompanyDetails.EasC13) AS FirstOfEasC13, CompanyDetails.PHAddressComplex, First(CompanyDetails.EasC14) AS FirstOfEasC14, CompanyDetails.PHAddressStrNo, First(CompanyDetails.EasC15) AS FirstOfEasC15, CompanyDetails.[PHAddressStreet/Farm] AS AddressCO, First(CompanyDetails.EasC16) AS FirstOfEasC16, CompanyDetails.PHAddressCity, First(CompanyDetails.EasC17) AS FirstOfEasC17, CompanyDetails.PHAddressZIP AS ZIPCO, 9999 AS c18, 3010 AS c19, "7020737716" & [FinancialYear] & Format([Forms]![EMP501]![ToDate],"mm") & "0000000" & Right("000000" & [Employees Detail]![Emp#],7) AS CertNo, 3015 AS c20, IIf(Sum([Tax(PAYE)])<>0,"IRP5","IT3(a)") AS IRP5, 3020 AS c21, EmployeesDetailQ.NatureOfPerson AS Nature, 3025 AS c22, [Salaries YTD].financialYear AS YearE, IIf([ETI1]<>0,"3026","") AS c23, IIf([ETI1]<>0,"X","") AS ETI, IIf([ETI1]<>0,"4118","") AS C76, ETISumQ.SumOfETI1 AS EtiSum, 3030 AS c24, EmployeesDetailQ.LastName, 3040 AS c25, [FirstName] & " " & [MiddleName] AS FirstN, 3050 AS c26, EmployeesDetailQ.Initials, 3060 AS c27, IIf([IDType]="SA ID",[ID#],"") AS IDNo, 3070 AS c28, IIf([IDType]="Passport",[ID#],"") AS PassPortNo, 3075 AS c29, IIf([IDType]="Passport",[CountryIssued],Null) AS Country, 3080 AS c30, Format([BirthDate],"yyyymmdd") AS DoB, 3100 AS c31, EmployeesDetailQ.TaxRefNumber, 3263 AS c32, CompanyDetails.SIC7 AS SIC7E, 3264 AS c33, CompanyDetails.SEZ AS SEZE, 3125 AS c34, EmployeesDetailQ.Email, 3136 AS c35, CompanyDetails.Phone, 3138 AS c36, EmployeesDetailQ.[Phone#], 3147 AS c37, CompanyDetails.[PHAddressStreet/Farm], 3149 AS c38, CompanyDetails.POAddressCity, 3150 AS c39, CompanyDetails.PHAddressZIP, 3160 AS c40, EmployeesDetailQ.[Emp#], 3170 AS c41, IIf([DateOfEngagment]>[Forms]![EMP501]![FromDate],Format([DateOfEngagment],"yyyymmdd"),Format([Forms]![EMP501]![FromDate],"yyyymmdd")) AS EmployPeriodFrom, 3180 AS c42, IIf([DateOfTermination]<[Forms]![EMP501]![ToDate],Format([DateOfTermination],"yyyymmdd"),Format([Forms]![EMP501]![ToDate],"yyyymmdd")) AS EmployPeriodTo, 3200 AS c43, IIf([EmployeesDetailQ]![PayPeriod]=2,"52.0000",IIf([EmployeesDetailQ]![PayPeriod]=4,"12.0000")) AS PayPeriodE, 3210 AS c44, IIf([EmployeesDetailQ]![PayPeriod]=2,DateDiff("w",IIf([DateOfEngagment]>[Forms]![EMP501]![FromDate],[DateOfEngagment],[Forms]![EMP501]![FromDate]),IIf([DateOfTermination]<[Forms]![EMP501]![ToDate],[DateOfTermination],[Forms]![EMP501]![ToDate])),IIf([EmployeesDetailQ]![PayPeriod]=4,Int(DateDiff("m",IIf([DateOfEngagment]>[Forms]![EMP501]![FromDate],[DateOfEngagment],[Forms]![EMP501]![FromDate]),IIf([DateOfTermination]<[Forms]![EMP501]![ToDate],[DateOfTermination],[Forms]![EMP501]![ToDate]))))) AS PeriodsWork, 3214 AS c45, EmployeesDetailQ.AddressStreet, 3216 AS c46, EmployeesDetailQ.AddressTown, 3217 AS c47, EmployeesDetailQ.ZipCode, 3218 AS c48, IIf([PostAddress]=0,"","X") AS PostSameRes, IIf([PostAddress]=0,"3247","") AS c49, IIf([PostAddress]=0,"N","") AS PostAddress2, IIf([PostAddress]=0,"3249","") AS c50, IIf([StreetAddress] Or [PostAddress]=0,"X","") AS POBox, IIf([PrivatBag]=1,"3250") AS C51, IIf([PrivatBag]=1,"X","") AS PrivatBagYN, IIf([PostAddress]=0,"3253","") AS c52, IIf([StreetAddress]=0,[POCity],"") AS PostOffice, IIf([PostAddress]=0,"3254","") AS c53, IIf([StreetAddress]=0,[POZIPCity],"") AS POZip, IIf([StreetAddress] Or [PostAddress]=0,"3262") AS c54, EmployeesDetailQ.[POBox/PrivBagNo], 3240 AS c58, IIf([AccountType] Is Null,0,IIf([AccountType]=1,1,IIf([AccountType]=2,2,5))) AS AccountT, IIf([BankAccount]=-1,"3241","") AS c59, EmployeesDetailQ.[Account#], IIf([BankAccount]=-1,"3242","") AS c60, EmployeesDetailQ.BranchCode, IIf([BankAccount]=-1,"3243","") AS c61, EmployeesDetailQ.BankName, IIf([BankAccount]=-1,"3244","") AS C73, EmployeesDetailQ.Branch AS BranchName, IIf([BankAccount]=-1,"3245","") AS c62, IIf([BankAccount]=-1,[FirstName] & " " & [LastName],"") AS AccountName, IIf([BankAccount]=-1,"3246","") AS c63, IIf([BankAccount]=-1,1) AS BankRel, First(3601) AS c64, (Sum(([Gross]))) AS Income, IIf(Sum([Salaries YTD]![PensionFundE])<>0,"3697","3698") AS c65, (Sum(([Gross]))) AS gross1, IIf(Int(Sum([Salaries YTD]![PensionfundE]))=0,"",First(4001)) AS c66, IIf(Int(Sum([Salaries YTD]![PensionfundE]))=0,"",Int(Sum([Salaries YTD]![PensionfundE]))) AS PensionDed, IIf(Int(Sum([Salaries YTD]![PensionfundE]))=0,"",First(4497)) AS c67, IIf(Int(Sum([Salaries YTD]![PensionfundE]))=0,"",Int(Sum([Salaries YTD]![PensionfundE]))) AS TotalDed, First(4141) AS c68, (Sum([uif]+[uifco])) AS UifD, First(4142) AS C69, Round(Sum([Gross]*0.01),2) AS SDL, First(4149) AS c70, Round(Sum([Gross]*0.01),2)+Sum([uif]+[uifco]+[tax(paye)]) AS TotaldedCE, IIf([PAYE]<>0,4102,"") AS C74, IIf(([PAYE])<>0,([Paye]),"") AS PAYE1, IIf(Sum([PAYE])=0,First(4150),"") AS C71, IIf(Sum([PAYE])=0,"02","") AS ReasonDed, First(9999) AS c72
FROM ETISumQ RIGHT JOIN ((EmployeesDetailQ INNER JOIN [Salaries YTD] ON EmployeesDetailQ.[Emp#]=[Salaries YTD].[Emp#]) INNER JOIN CompanyDetails ON EmployeesDetailQ.Company=CompanyDetails.Code) ON ETISumQ.[Emp#]=[Salaries YTD].[Emp#]
WHERE ((([Salaries YTD].PayDate) Between [Forms]![EMP501]![FromDate] And [Forms]![EMP501]![ToDate]))
GROUP BY CompanyDetails.CompanyName, "Live", CompanyDetails.PAYENo, CompanyDetails.SDLNo, CompanyDetails.UIFNo, CompanyDetails.ContactPerson, CompanyDetails.PhoneContact, CompanyDetails.EmailContact, "In-House", [Salaries YTD].financialyear, Format([Forms]![EMP501]![ToDate],"yyyymm"), CompanyDetails.SIC7, CompanyDetails.SEZ, CompanyDetails.TradeClass, CompanyDetails.PHAddressUnit, CompanyDetails.PHAddressComplex, CompanyDetails.PHAddressStrNo, CompanyDetails.[PHAddressStreet/Farm], CompanyDetails.PHAddressCity, CompanyDetails.PHAddressZIP, 9999, 3010, "7020737716" & [FinancialYear] & Format([Forms]![EMP501]![ToDate],"mm") & "0000000" & Right("000000" & [Employees Detail]![Emp#],7), 3015, 3020, EmployeesDetailQ.NatureOfPerson, 3025, [Salaries YTD].financialYear, IIf([ETI1]<>0,"3026",""), IIf([ETI1]<>0,"X",""), IIf([ETI1]<>0,"4118",""), ETISumQ.SumOfETI1, 3030, EmployeesDetailQ.LastName, 3040, [FirstName] & " " & [MiddleName], 3050, EmployeesDetailQ.Initials, 3060, IIf([IDType]="SA ID",[ID#],""), 3070, IIf([IDType]="Passport",[ID#],""), 3075, IIf([IDType]="Passport",[CountryIssued],Null), 3080, Format([BirthDate],"yyyymmdd"), 3100, EmployeesDetailQ.TaxRefNumber, 3263, CompanyDetails.SIC7, 3264, CompanyDetails.SEZ, 3125, EmployeesDetailQ.Email, 3136, CompanyDetails.Phone, 3138, EmployeesDetailQ.[Phone#], 3147, CompanyDetails.[PHAddressStreet/Farm], 3149, CompanyDetails.POAddressCity, 3150, CompanyDetails.PHAddressZIP, 3160, EmployeesDetailQ.[Emp#], 3170, IIf([DateOfEngagment]>[Forms]![EMP501]![FromDate],Format([DateOfEngagment],"yyyymmdd"),Format([Forms]![EMP501]![FromDate],"yyyymmdd")), 3180, IIf([DateOfTermination]<[Forms]![EMP501]![ToDate],Format([DateOfTermination],"yyyymmdd"),Format([Forms]![EMP501]![ToDate],"yyyymmdd")), 3200, IIf([EmployeesDetailQ]![PayPeriod]=2,"52.0000",IIf([EmployeesDetailQ]![PayPeriod]=4,"12.0000")), 3210, IIf([EmployeesDetailQ]![PayPeriod]=2,DateDiff("w",IIf([DateOfEngagment]>[Forms]![EMP501]![FromDate],[DateOfEngagment],[Forms]![EMP501]![FromDate]),IIf([DateOfTermination]<[Forms]![EMP501]![ToDate],[DateOfTermination],[Forms]![EMP501]![ToDate])),IIf([EmployeesDetailQ]![PayPeriod]=4,Int(DateDiff("m",IIf([DateOfEngagment]>[Forms]![EMP501]![FromDate],[DateOfEngagment],[Forms]![EMP501]![FromDate]),IIf([DateOfTermination]<[Forms]![EMP501]![ToDate],[DateOfTermination],[Forms]![EMP501]![ToDate]))))), 3214, EmployeesDetailQ.AddressStreet, 3216, EmployeesDetailQ.AddressTown, 3217, EmployeesDetailQ.ZipCode, 3218, IIf([PostAddress]=0,"","X"), IIf([PostAddress]=0,"3247",""), IIf([PostAddress]=0,"N",""), IIf([PostAddress]=0,"3249",""), IIf([StreetAddress] Or [PostAddress]=0,"X",""), IIf([PrivatBag]=1,"3250"), IIf([PrivatBag]=1,"X",""), IIf([PostAddress]=0,"3253",""), IIf([StreetAddress]=0,[POCity],""), IIf([PostAddress]=0,"3254",""), IIf([StreetAddress]=0,[POZIPCity],""), IIf([StreetAddress] Or [PostAddress]=0,"3262"), EmployeesDetailQ.[POBox/PrivBagNo], 3240, IIf([AccountType] Is Null,0,IIf([AccountType]=1,1,IIf([AccountType]=2,2,5))), IIf([BankAccount]=-1,"3241",""), EmployeesDetailQ.[Account#], IIf([BankAccount]=-1,"3242",""), EmployeesDetailQ.BranchCode, IIf([BankAccount]=-1,"3243",""), EmployeesDetailQ.BankName, IIf([BankAccount]=-1,"3244",""), EmployeesDetailQ.Branch, IIf([BankAccount]=-1,"3245",""), IIf([BankAccount]=-1,[FirstName] & " " & [LastName],""), IIf([BankAccount]=-1,"3246",""), IIf([BankAccount]=-1,1), IIf([PAYE]<>0,4102,""), IIf(([PAYE])<>0,([Paye]),""), IIf([ETI1]<>0,"4118",""), IIf([SumOfETI1]<>0,[SumOfETI1],"")
HAVING (((EmployeesDetailQ.[Emp#])=100 Or (EmployeesDetailQ.[Emp#])=1));
 
fnETI, something rings a bell, far far away... but no real clue for what you are on about :(
If I helped you before, I probably said something along the lines of readable code is maintable code....
That SQL for sure is unreadable! (atleast to me who isnt "into" your db/problem)


About this thread, not exactly sure what you expect me/us to do with the SQL....
Also I still have my reservations about removing the empty columns, would seem to be breaking the csv logic... but if its your requirement....
 
I didn't mean you to do anything, but last time you helped me I was there too and you said exactly what you just repeated. Just posted it for your laugh really and your "I told you so". :)
Take care
 
Dont think I am a person to say "I told you so"....
 
No your not, you are always encouraging and very helpful.
Apologies there was no intention of insulting you. I somehow thought you remember helping me before (you have a few times) and because I neglected all the advise you gave me than, I posted the code for purpose mentioned earlier.
Thanks again I do appreciate your help enormously.
 
Cant even start to try to remember even 10% of my posts on this forum, sorry if that makes it seem like I dont care... problem is I do care ( too much, most of the time )
 
Can anybody tell me what's the difference between the below two codes except the length and some fields. I can't understand that one loop works with identical code and the other one tells me "loop without do".
This one is working:
Code: 
Sub sbtestExport()
    ' create your own csv
    'DoCmd.OpenQuery "QEMP501Comp", , acEdit
    
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim myLine As String
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("QEMP501Comp")
    
    
    '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
        Debug.Print myLine
    Loop
    
    
End Sub

This one gives me Compile error "Loop without Do"
Code: 
Sub Test1()
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim myLine As String
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("QEmpEx")

    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
        If Not IsNull(rs!PHWorkEmp) Then
            myLine = myLine & rs!PHWorkEmp & ","
        End If
        If Not IsNull(rs!C19) Then
            myLine = myLine & rs!C19 & ","
        End If
        If Not IsNull(rs!PHBusTownEmp) Then
            myLine = myLine & rs!PHBusTownEmp & ","
        End If
        If Not IsNull(rs!C20) Then
            myLine = myLine & rs!C20 & ","
        End If
        If Not IsNull(rs!PHZipEmp) Then
            myLine = myLine & rs!PHZipEmp & ","
        End If
        If Not IsNull(rs!C21) Then
            myLine = myLine & rs!C21 & ","
        End If
        If Not IsNull(rs!EmpNo) Then
            myLine = myLine & rs!EmpNo & ","
        End If
        If Not IsNull(rs!C22) Then
            myLine = myLine & rs!C22 & ","
        End If
        If Not IsNull(rs!AssPStart) Then
            myLine = myLine & rs!AssPStart & ","
        End If
        If Not IsNull(rs!C23) Then
            myLine = myLine & rs!C23 & ","
        End If
        If Not IsNull(rs!AssPEnd) Then
            myLine = myLine & rs!AssPEnd & ","
        End If
        If Not IsNull(rs!C24) Then
            myLine = myLine & rs!C24 & ","
        End If
        If Not IsNull(rs!PayPYear) Then
            myLine = myLine & rs!PayPYear & ","
        End If
        If Not IsNull(rs!C25) Then
            myLine = myLine & rs!C25 & ","
        End If
        If Not IsNull(rs!PayPWork) Then
            myLine = myLine & rs!PayPWork & ","
        End If
        If Not IsNull(rs!C26) Then
            myLine = myLine & rs!C26 & ","
        End If
        If Not IsNull(rs!EmpResAdd) Then
            myLine = myLine & rs!EmpResAdd & ","
        End If
        If Not IsNull(rs!C27) Then
            myLine = myLine & rs!C27 & ","
        End If
        If Not IsNull(rs!EmpResTown) Then
            myLine = myLine & rs!EmpResTown & ","
        End If
        If Not IsNull(rs!C28) Then
            myLine = myLine & rs!C28 & ","
        End If
        If Not IsNull(rs!EmpResZip) Then
            myLine = myLine & rs!EmpResZip & ","
        End If
        If Not IsNull(rs!C29) Then
            myLine = myLine & rs!C29 & ","
        End If
        If Not IsNull(rs!PostSameRes) Then
            myLine = myLine & rs!PostSameRes & ","
        End If
        If Not IsNull(rs!C30) Then
            myLine = myLine & rs!C30 & ","
        End If
        If Not IsNull(rs!StreetAddYN) Then
            myLine = myLine & rs!StreetAddYN & ","
        End If
        If Not IsNull(rs!C31) Then
            myLine = myLine & rs!C31 & ","
        End If
        If Not IsNull(rs!POBoxYN) Then
            myLine = myLine & rs!POBoxYN & ","
        End If
        If Not IsNull(rs!C32) Then
            myLine = myLine & rs!C32 & ","
        End If
        If Not IsNull(rs!POBagYN) Then
            myLine = myLine & rs!POBagYN & ","
        End If
        If Not IsNull(rs!C33) Then
            myLine = myLine & rs!C33 & ","
        End If
        If Not IsNull(rs!PostOffice) Then
            myLine = myLine & rs!PostOffice & ","
        End If
        If Not IsNull(rs!C34) Then
            myLine = myLine & rs!C34 & ","
        End If
        If Not IsNull(rs!PostZip) Then
            myLine = myLine & rs!PostZip & ","
        End If
        If Not IsNull(rs!C35) Then
            myLine = myLine & rs!C35 & ","
        End If
        If Not IsNull(rs!BoxBagNO) Then
            myLine = myLine & rs!BoxBagNO & ","
        End If
        If Not IsNull(rs!C36) Then
            myLine = myLine & rs!C36 & ","
        End If
        If Not IsNull(rs!BankAccType) Then
            myLine = myLine & rs!BankAccType & ","
        End If
        If Not IsNull(rs!c38) Then
            myLine = myLine & rs!c38 & ","
        End If
        If Not IsNull(rs!BankAccNo) Then
            myLine = myLine & rs!BankAccNo & ","
        End If
        If Not IsNull(rs!c39) Then
            myLine = myLine & rs!c39 & ","
        End If
        If Not IsNull(rs!BranchCode) Then
            myLine = myLine & rs!BranchCode & ","
        End If
        If Not IsNull(rs!c40) Then
            myLine = myLine & rs!c40 & ","
        End If
        If Not IsNull(rs!BankName) Then
            myLine = myLine & rs!BankName & ","
        End If
        If Not IsNull(rs!c41) Then
            myLine = myLine & rs!c41 & ","
        End If
        If Not IsNull(rs!BranchName) Then
            myLine = myLine & rs!BranchName & ","
        End If
        If Not IsNull(rs!c42) Then
            myLine = myLine & rs!c42 & ","
        End If
        If Not IsNull(rs!AccHolder) Then
            myLine = myLine & rs!AccHolder & ","
        End If
        If Not IsNull(rs!c43) Then
            myLine = myLine & rs!c43 & ","
        End If
        If Not IsNull(rs!AccRelation) Then
            myLine = myLine & rs!AccRelation & ","
        End If
        If Not IsNull(rs!c44) Then
            myLine = myLine & rs!c44 & ","
        End If
        If Not IsNull(rs!Income) Then
            myLine = myLine & rs!Income & ","
        End If
        If Not IsNull(rs!c45) Then
            myLine = myLine & rs!c45 & ","
        End If
        If Not IsNull(rs!Gross) Then
            myLine = myLine & rs!Gross & ","
        End If
        If Not IsNull(rs!c46) Then
            myLine = myLine & rs!c46 & ","
        End If
        If Not IsNull(rs!PensionDed) Then
            myLine = myLine & rs!PensionDed & ","
        End If
        If Not IsNull(rs!c47) Then
            myLine = myLine & rs!c47 & ","
        End If
        If Not IsNull(rs!TotalDed) Then
            myLine = myLine & rs!TotalDed & ","
        End If
        If Not IsNull(rs!c48) Then
            myLine = myLine & rs!c48 & ","
        End If
        If Not IsNull(rs!UIFDed) Then
            myLine = myLine & rs!UIFDed & ","
        End If
        If Not IsNull(rs!c49) Then
            myLine = myLine & rs!c49 & ","
        End If
        If Not IsNull(rs!SDL) Then
            myLine = myLine & rs!SDL & ","
        End If
        If Not IsNull(rs!c50) Then
            myLine = myLine & rs!c50 & ","
        End If
        If Not IsNull(rs!TotalDedCE) Then
            myLine = myLine & rs!TotalDedCE & ","
        End If
        If Not IsNull(rs!c51) Then
            myLine = myLine & rs!c51 & ","
        End If
        If Not IsNull(rs!ReasonCode) Then
            myLine = myLine & rs!ReasonCode & ","
        End If
        If Not IsNull(rs!c52) Then
            myLine = myLine & rs!c52 & ","
        End If
        If Not IsNull(rs!Paye) Then
            myLine = myLine & rs!Paye & ","
        End If
        If Not IsNull(rs!C78End) Then
            myLine = myLine & rs!C78End & ","
        
        ' Remove the last (useless comma)
        myLine = Left(myLine, Len(myLine) - 1)
        'Write #1, myLine ' Or was it print #1, Myline ???
        rs.MoveNext
        Debug.Print myLine
    Loop
    
End Sub

Beats me :banghead:
Thanks for any help
 
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
 
Little thing to note -->
Chintsapete said:
Code: 
    Set db = CurrentDb
    Set rs = [COLOR="red"]CurrentDb[/COLOR].OpenRecordset("QEMP501Comp")
Click to expand...

Chintsapete said:
Code: 
    Set db = CurrentDb
    Set rs = [COLOR="blue"]db[/COLOR].OpenRecordset("QEMP501Comp")
Click to expand...
You would make even quicker progress if you do it all in a query and use a ParramArray to concatenate your values.

By the way, how does the system know which field corresponds to what if the delimiters in each record differ?
 
You must log in or register to reply here.

Similar threads

R
Exporting Date field without a time and without a delimiter
Replies
1
Views
830
Ranman256
Ranman256
S
Export to CSV blank fields with Text Qualifier
Replies
14
Views
5,666
vbaInet
V

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom