By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

Exporting Empty fields into .txt

P: 18
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.
Oct 20 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 20 '06 #2

P: 18
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.
Oct 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 20 '06 #4

P: 18
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.
Oct 20 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 20 '06 #6

P: 18
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."
Oct 20 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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."
Oct 20 '06 #8

P: 18
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.
Oct 20 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 20 '06 #10

P: 18
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.
Oct 23 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function readTextFile(ByVal fpath As String)
  3. On Error GoTo Err_readTextFile
  4. Dim db As Database
  5. Dim rs As Recordset
  6. Dim fhandle1 As Integer
  7. Dim fline As String
  8. Dim tempValue1 As String
  9. Dim tempValue2 As String
  10. Dim tempValue3 As String
  11. Dim i As Integer
  12.     ' open the text file to be read
  13.     fhandle1 = FreeFile()
  14.     Open fpath For Input Access Read Lock Write As #fhandle1
  15.  
  16.     Set db = CurrentDb
  17.     Set rs = db.OpenRecordset("NewTableName") ' replace with your table name
  18.  
  19.  
  20.     Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
  21.         Line Input #fhandle1, fline
  22.         fline = Trim(fline)  ' cut off spaces
  23.  
  24.         If IsNumeric(Left(fline,1)) Then ' this will ignore headers
  25.             tempValue = ""
  26.  
  27.             For i=0 To Len(fline)-1
  28.                 If (Mid(fline,i,1) <> " ") And (i < 21) Then
  29.                     tempValue1=tempValue1 & Mid(fline,i,1)
  30.                 ElseIf (Mid(fline,i,1) <> " ") And (i >= 21 And i < 35) Then
  31.                     tempValue2=tempValue2 & Mid(fline,i,1)
  32.                 ElseIf (Mid(fline,i,1) <> " ") And (i >= 35) Then
  33.                     tempValue3=tempValue3 & Mid(fline,i,1)
  34.                 Else
  35.                 End If
  36.  
  37.                 rs.AddNew
  38.                 rs!ESI_ID = tempValue1
  39.                 rs!CR_DUNS_NO = tempValue2
  40.                 rs!BGN06 = tempValue3
  41.                 rs.Update
  42.             Next i    
  43.          End If   
  44.     Loop
  45.  
  46.     Close #fhandle1
  47.     Close #fhandle2
  48.  
  49.     rs.Close
  50.     Set rs=Nothing
  51.     Set db=Nothing
  52.  
  53. Exit_readTextFile:
  54. Exit Function
  55. Err_readTextFile:
  56.     Select Case Err
  57.     Case 0      '.insert Errors you wish to ignore here
  58.         Resume Next
  59.     Case Else   '.All other errors will trap
  60.         Beep
  61.         MsgBox Err.Description, , "Error in Function Text File Handler.readTextFile"
  62.         Resume Exit_readTextFile
  63.     End Select
  64.     Resume 0    '.FOR TROUBLESHOOTING
  65. End Function
  66.  
  67.  
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
Oct 23 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.