Creating a CSV file from access - Using (full stop)

[darinmc]

Hi
I have sort of asked this question before and was just wondering if it is possible at all???

I'm trying to get the data into a Sage Payroll CSV file, one of the problems is that the field name is:

Contact Telephone No.

in the query below, part of the code is:

Null AS [Contact Telephone No],

I have tried quite a few different things but it just WONT allow me to use the . (dot)
Is there away to insert it???


Pls and thx
Darin

 

[MajP]

If I understand correctly it has to be in this naming format because Sage Payroll uses that naming convention.

Once you run this query what do you do?
1)Directly link to the CSV
2)Export
3)Create table
etc.

I am not sure if you can force that naming convention, hopefully someone can answer it. I never use spaces, reserved words, punctuation in any database or variable name. But depending on how you use it you could simply manipulate the CSV file through code and change the name on the back end.

 

[darinmc]

Hi
Its a query I have in the database, I then click a command button to export as a txt file without headers.

I would like to export it as a csv file with headers.

The code is: (using the docmd.transfertext)

Private Sub cmdNewEmptxt_Click()
Dim StrFile, StrFileName, StrEmpNo, StrEmpName
'--- Lookup Password on Switchboard, allow access to specific forms ---
Dim VarPassword As Variant
Dim stDocName As String
Dim stLinkCriteria As String
VarPassword = DLookup("[NewSageEmp]", "tblPassword", "[PasswordID] = [Forms]![frmSwitchboard]![tPassID]")
StrEmpNo = DLookup("[EmpNo]", "tblEmployee", "[EmpRegNo] = [Forms]![frmPayrollUpdates]![RegNo]")
StrEmpName = DLookup("[EmpForeName]", "tblEmployee", "[EmpRegNo] = [Forms]![frmPayrollUpdates]![RegNo]")
StrFileName = StrEmpName & StrEmpNo 'Taken from query
StrFile = "\\Lynxserver\lynx\Payroll\NewEmployeesSAGE\Emp-" & StrFileName & ".txt" 'txt csv

If VarPassword = 0 Then
'message box
MsgBox "Please contact payroll"
Else
'True - displays headings
DoCmd.TransferText acExportDelim, , "SAGE EmployeeDetailsTemplate", StrFile, False
'\\Lynxserver\lynx\Payroll\NewEmployeesSAGE
End If

Exit_cmdNewEmptxt_Click:
    Exit Sub
'
Err_cmdNewEmptxt_Click:
    MsgBox Err.Description
    Resume Exit_cmdNewEmptxt_Click
End Sub

I suppose like u say, I could try write the sql out in full and where the sql part is:
Null AS [Contact Telephone No],
Would it be possible to use something like?

Dim StrContactTel as String
StrContactTel = Contact Telephone No.
then add it to above:
Null AS StrContactTel,

Thx
Darin

 

[traingamer]

Export it without headers, then use file system objects or (open #) to open the file, and output the header line, followed by the rest of the lines (Ok if relatively low number of lines).

Or continue to export it without headers, but make "SAGE EmployeeDetailsTemplate" a union query where the first row of data is always your header in text form. You may or may not need to work on an order by clause.

SELECT Top 1  'DefID' as Id, 'Form Name.' as FormName from defaults
union
SELECT Defaults.Id, Defaults.FormName
FROM Defaults;

(It's access - there's always more than one way.)

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[darinmc]

Is there a way of exporting from access directly into the template, CSV file?

I have been thinking of an easier solution and more practical one... Within a week, there could be between 1 and 15 + employees, what would be ideal is:
When exporting from access, if i could do it directly into the csv file INTO the 1st available free line, (After the header)
therefore
each time i did an export it would keep adding on to the CSV template file...
Usually every Tuesday, I would import into sage payroll (ALL the new starters)?

if access cant directly put the info directly into the CSV file, then maybe a macro that would fetch ALL the seperate new CSV employee files and place them into 1 template file?

OR any solution you think is easiest? Remembering I need or the the new employees in 1 CSV template file...
Thx
Darin