Exporting Empty fields into .txt

[danibecr]

Hello all,

I'm trying to export a the results of a query into a comma delimited .txt with the header colum. The fist column is always blank.The current format is

Header:
XXXX {Tab} XXX_XX {Tab} {Tab} {Tab} XX_XXXX_XX {Tab} XXXXX
Body:
{Tab}xxxxxxxxxxxxxxxxxx {Tab}xxxxxxx{Tab}xxxxxxxxx

I'm exporting the results of a make table query in the same format with comma's as the delimiter. Problem is in the header there is two delimiters between the second and third text column. I cant get the specs right to export a blank column. I'v tried using a , as the column name but the export comes out with an underhyphen as the name not a comma.

[MMcCarthy]

Try using the | as the delimiter

Hello all,

I'm trying to export a the results of a query into a comma delimited .txt with the header colum. The fist column is always blank.The current format is

Header:
XXXX {Tab} XXX_XX {Tab} {Tab} {Tab} XX_XXXX_XX {Tab} XXXXX
Body:
{Tab}xxxxxxxxxxxxxxxxxx {Tab}xxxxxxx{Tab}xxxxxxxxx

I'm exporting the results of a make table query in the same format with comma's as the delimiter. Problem is in the header there is two delimiters between the second and third text column. I cant get the specs right to export a blank column. I'v tried using a , as the column name but the export comes out with an underhyphen as the name not a comma.

[danibecr]

Working for a utility company and we were only allowed to test transactions once using a comma and a period. I don't know if the receiving system will accept a pipe for the delimiter.

[MMcCarthy]

When you do the export click on the advanced tab. You will see a list of the fields displayed and can manipulate them, even hide them. You can also save your changes as a specification for future use.

Hello all,

I'm trying to export a the results of a query into a comma delimited .txt with the header colum. The fist column is always blank.The current format is

Header:
XXXX {Tab} XXX_XX {Tab} {Tab} {Tab} XX_XXXX_XX {Tab} XXXXX
Body:
{Tab}xxxxxxxxxxxxxxxxxx {Tab}xxxxxxx{Tab}xxxxxxxxx

I'm exporting the results of a make table query in the same format with comma's as the delimiter. Problem is in the header there is two delimiters between the second and third text column. I cant get the specs right to export a blank column. I'v tried using a , as the column name but the export comes out with an underhyphen as the name not a comma.

[danibecr]

Specifications in Fixed Width won't let me have a header and Specifications in Delimited wont let me have a nameless column in the header.

[MMcCarthy]

Specifications in fixed width do let you have a header.

In the normal wizard on fixed width, when you get to the window that shows the column. double click where you want the second column to appear and it will be created.

Then go to advanced and name/rename it

Specifications in Fixed Width won't let me have a header and Specifications in Delimited wont let me have a nameless column in the header.

[danibecr]

Forgive me for my ignorance but the only boxs I'm seeing is the Sample export Format with the Fixed Width radio button clicked. Or under advanced when I add a column with no name I get the error : "The number of fields in your export specs does not match the number of fields in the table you have chosen to export."

[MMcCarthy]

What exactly are you trying to export?
Why do you need an empty field?
Open your query in design view, then change to SQL view and copy and paste the code here.

Forgive me for my ignorance but the only boxs I'm seeing is the Sample export Format with the Fixed Width radio button clicked. Or under advanced when I add a column with no name I get the error : "The number of fields in your export specs does not match the number of fields in the table you have chosen to export."

[danibecr]

o.k. I have a table with 4 colums SOAR ESI_ID CR_DUNS_NO BGN06
The SOAR column will never have any information in it. The ESI_ID column will be 17 numbers wide, the CR_DUNS_NO can have up to 15 numbers in it and the BGN06 column can have up to like 25 or 30 alpha or numeric. Right now I run a SQL query in a different program and export it to EXCEL. Then I copy the ESI_ID, CR_DUNS_NO, and BGN06 and paste it to a .txt file. The .txt already has a header and I paste my data under the header. Then I have to insert a Tab in front of every line to make the data line up for the last three colums and leave the first column empty. All in all it needs to look like this

SOAR,ESI_ID,,,CR_DUNS_NO,,BGN_06
, data , data, data

The SQL query is as follows

INSERT INTO 86702PayloadToDrop ( ESI_ID, CR_DUNS_NO, BGN06 )
SELECT [86702HU].ESI_ID, [86702HU].CR_DUNS_NO, [86702HU].BGN06
FROM 86702HU
WHERE ((([86702HU].[Run Date])=Date()-1));

It's an append query (sorry for misinforming earler) and the table appended to already has SOAR for the first column.

[MMcCarthy]

Why are you exporting the SQL query to excel why not send it directly to a text file.

I think the reason I can't seem to help is that I really don't know what you're doing.

Forgeting what you're doing at the moment. The more information you are giving me about sql to excel to text to ?? the more confused I'm getting.

Can you tell me what you need to happen in the future. You have a SQL query that much is fine.

What has to happen after that and what do you want to end up with exactly.

I really want to help but I'm very confused.

o.k. I have a table with 4 colums SOAR ESI_ID CR_DUNS_NO BGN06
The SOAR column will never have any information in it. The ESI_ID column will be 17 numbers wide, the CR_DUNS_NO can have up to 15 numbers in it and the BGN06 column can have up to like 25 or 30 alpha or numeric. Right now I run a SQL query in a different program and export it to EXCEL. Then I copy the ESI_ID, CR_DUNS_NO, and BGN06 and paste it to a .txt file. The .txt already has a header and I paste my data under the header. Then I have to insert a Tab in front of every line to make the data line up for the last three colums and leave the first column empty. All in all it needs to look like this

SOAR,ESI_ID,,,CR_DUNS_NO,,BGN_06
, data , data, data

The SQL query is as follows

INSERT INTO 86702PayloadToDrop ( ESI_ID, CR_DUNS_NO, BGN06 )
SELECT [86702HU].ESI_ID, [86702HU].CR_DUNS_NO, [86702HU].BGN06
FROM 86702HU
WHERE ((([86702HU].[Run Date])=Date()-1));

It's an append query (sorry for misinforming earler) and the table appended to already has SOAR for the first column.

[danibecr]

o.k. I use a Golden 32 program to run queries off of a SQL server. Golden 32 will export to Excel, CSV, or XML. Right now we run the query 2 times daily and save the results to an Excel file for data integrity. Then I align the data into a .txt file and put it into a folder. Twice a day an automated system sweeps the folder and imports the .txt file. Whatever data I have on the .txt file get's turned into the different transactions and gets sent off to whereever it goes. I'm trying to automate this so that we run the query, save it to a location, click a button in the database and it imports the xls, creates the table needed and exports a txt. The data results from the SQL query are as follows:

Column A Column B Column C
ESI_ID CR_DUNS_NO BGN06
1044372000XXXXXXX XXXXXXXXX XXXXXXXXXXXXXXXXXXXXX

The .txt file that I create looks like this, with a Tab between each field

SOAR ESIID REPID REPREF
1044372000XXXXXXX XXXXXXXXX XXXXXXXXXXXXXXXX

The .txt file that I need should look like

SOAR,ESIID,,,REPID,,REPREF
,1044372000XXXXXXX,XXXXXXXX,XXXXXXXXXXXXXXX

With 3 comma delimiters between ESIID and REPID and 2 comma delimiters between REPID and REPREF. Sorry for so much confusion.

[MMcCarthy]

Column A Column B Column C
ESI_ID CR_DUNS_NO BGN06
1044372000XXXXXXX XXXXXXXXX XXXXXXXXXXXXXXXXXXXXX

Put the following function in a module and run it to import the above to access having created an appropriate table with the following text fields

ESI_ID
CR_DUNS_NO
BGN06

 
Public Function readTextFile(ByVal fpath As String)
On Error GoTo Err_readTextFile
Dim db As Database
Dim rs As Recordset
Dim fhandle1 As Integer
Dim fline As String
Dim tempValue1 As String
Dim tempValue2 As String
Dim tempValue3 As String
Dim i As Integer
    ' open the text file to be read
    fhandle1 = FreeFile()
    Open fpath For Input Access Read Lock Write As #fhandle1
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("NewTableName") ' replace with your table name
 
 
    Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
        Line Input #fhandle1, fline
        fline = Trim(fline)  ' cut off spaces
 
        If IsNumeric(Left(fline,1)) Then ' this will ignore headers
            tempValue = ""
 
            For i=0 To Len(fline)-1
                If (Mid(fline,i,1) <> " ") And (i < 21) Then
                    tempValue1=tempValue1 & Mid(fline,i,1)
                ElseIf (Mid(fline,i,1) <> " ") And (i >= 21 And i < 35) Then
                    tempValue2=tempValue2 & Mid(fline,i,1)
                ElseIf (Mid(fline,i,1) <> " ") And (i >= 35) Then
                    tempValue3=tempValue3 & Mid(fline,i,1)
                Else
                End If
 
                rs.AddNew
                rs!ESI_ID = tempValue1
                rs!CR_DUNS_NO = tempValue2
                rs!BGN06 = tempValue3
                rs.Update
            Next i    
         End If   
    Loop
 
    Close #fhandle1
    Close #fhandle2
 
    rs.Close
    Set rs=Nothing
    Set db=Nothing
 
Exit_readTextFile:
Exit Function
Err_readTextFile:
    Select Case Err
    Case 0      '.insert Errors you wish to ignore here
        Resume Next
    Case Else   '.All other errors will trap
        Beep
        MsgBox Err.Description, , "Error in Function Text File Handler.readTextFile"
        Resume Exit_readTextFile
    End Select
    Resume 0    '.FOR TROUBLESHOOTING
End Function
 
 

This will give you a table of data in Access that you can manipulate and append to a table with the following fields as appropriate.

SOAR ESIID REPID REPREF

You can use substitute labels for the blank field labels like Expr1. If you get the above working I will show you how to export the results to a csv file as below.

SOAR,ESIID,,,REPID,,REPREF
,1044372000XXXXXXX,XXXXXXXX,XXXXXXXXXXXXXXX