Query/physical file headings

345 pts.
Tags:
CVS
Microsoft Excel
Physical File
Hi, I have a query that creates a file. I transfer the file to my C drive as a cvs file that I send out to a vendor. He would like headings on the file.....I can't seem to find out how to do this.....I know this is a silly question but how can I get the column headings on the file? If I import into Excel the headings are there, the vendor finds the imports work better via a cvs file as opposed to an Excel file. Thanks in advance!!

Answer Wiki

Thanks. We'll let you know when a new response is added.

1. Use the ODBC Data Source Administrator tool of Client Access to setup a DSN using the library that your file is in and the IP address of your as400.

2. Open Excel
3. Data -> From other Source -> From Microsoft Query (make sure Microsoft Query is installed. If it isn’t, just use the ms office cd of your company and install it for free).
3. Select the data source you just created in step 1 above
4. Choose the file from the list, click next until you get to a finish button, click finish
5. The data will now be imported into Excel
6. Save the file as a .csv
7. You now have a .csv file with headings in tact

Anytime you rerun the query and populate the file, you simply go into your saved Excel sheet and click Data -> Refresh with the cursor on a line of data and your spreadsheet will refresh using your data source. You then resave as a .csv, etc

My directions above are brief, but I have typed instructions I did for some of my users if you need me to email them to you.

adam

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rmason
    Sorry, This is an AS400 query that is creating the file for the export.......
    345 pointsBadges:
    report
  • philpl1jb
    Method 1 - really low volumn Once the file is on your C drive edit it, I would recommend NOTEPAD but you can use WORDPAD just make sure that you save it as a TEXT file. Press enter at the top of the file so you have a blank line. Type in your headings with commas between them. SAVE AS A TEXT FILE with CVS extension. Method 2 -- you can keep your headings A low volumn method. Make a test file using notepad on your C: drive with just the headings..remember to enter them as .CSV - comma's between them and end with a Carriage return and name the file hdr.csv Then use the DOS copy command to create a combined file From DOS PROMPT COPY Hdr.CVS + Dtl.CVS Out.CVS Phil You can check it by opening it in Excel
    51,355 pointsBadges:
    report
  • philpl1jb
    Excellent, thanks. Phil
    51,355 pointsBadges:
    report
  • Rmason
    Thanks Guys, Adam, could you send me the detailed instructions? Thanks in advance!!!! Rick
    345 pointsBadges:
    report
  • Cwc
    Instead of having to manually type in all the column headings and maintain them, you could use the QUSLFLD API that will retrieve various field information from a file, including the field text and column headings. Then, via the QSH command, you could create a file in the IFS and then insert those headings into that file. CPYTOIMPF could then populate the IFS file with your physical file records. Ideally, you could package the API into a service program for reusability (as we have done in our shop). It would take some initial development, but once done, you'd have a flexible method for future needs with less coding and maintenance required.
    4,290 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following