UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA To Change Column Name On Csv Export And To Append To Csv, Access 2010    
 
   
alopez20
post Mar 20 2017, 01:27 PM
Post#1



Posts: 28
Joined: 28-November 12



I have 2 things. First, I am exporting an Access table to .csv with repeating headers - so in my table is looks like: 459-ER1 459-ER2 459-ER3 459-ER4 But in the exported file i need to rename it 459-ER 459-ER 459-ER 459-ER
so the same field name will appear 4 times.
This is my current export method:
DoCmd.TransferText acExportDelim, "CONS_FILE_Export Specification", _
"CONS_FILE_NR", UserInput & FileName & fileExt & ".csv", True

2nd, I need to append data from another table including the header and the columns arent the same as the first - to the above file so it looks like
SCENARIOID Reversal SCARD
14949HSTDD0NHUA3 N SCARD
15050HSTDD0NHUA3 N SCARD
15151HSTDD0NHUA3 N SCARD
15252HSTDD0NHUA3 N SCARD
15353HSTDD0NHUA3 N SCARD
15454HSTDD0NHUA3 N SCARD
15555HSTDD0NHUA3 N SCARD
15656HSTDD0NHUA3 N SCARD
ROW TEMPLATE TestRunID
1 blah blah
2 blah blah
3 blah blah

So it's all together in one file, regardless if it lines up
Thanks Amy
This post has been edited by alopez20: Mar 20 2017, 01:32 PM
Go to the top of the page
 
theDBguy
post Mar 20 2017, 02:16 PM
Post#2


Access Wiki and Forums Moderator
Posts: 69,630
Joined: 19-June 07
From: SunnySandyEggo


Hi,

To add to an existing Text file, check out the Open and Write statements. Once you are able to do this, you should be able to export the CSV without a header and then combine it with a template file containing the new header names.

Just a thought...

--------------------
Go to the top of the page
 
alopez20
post Mar 20 2017, 02:54 PM
Post#3



Posts: 28
Joined: 28-November 12



Do you mean search here for open and write?

Also, I want the header when I append it. i want data set A with all it's headers, then data set B appended with all its headers immediately under it
Go to the top of the page
 
theDBguy
post Mar 20 2017, 03:11 PM
Post#4


Access Wiki and Forums Moderator
Posts: 69,630
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I meant to search for the topics either online or in the Help Topics within Access.

Didn't you say you want to change the name of the headers from the table or query? If so, then I think, since you might end up using the Open and Write statements to APPEND other data sets to the same CSV file, it might be easier to simply APPEND the headers too because I am not sure there is a way to automatically rename the column headers at the same time data is being exported. In a sense, the process might go something like this:

Open text file
APPEND first header names
APPEND first data set
APPEND second header names
APPEND second data set
and so on...
Close text file

Just my 2 cents...

--------------------
Go to the top of the page
 
alopez20
post Mar 21 2017, 10:58 AM
Post#5



Posts: 28
Joined: 28-November 12



Can you give me an example of what the code may look like?
Go to the top of the page
 
theDBguy
post Mar 21 2017, 11:28 AM
Post#6


Access Wiki and Forums Moderator
Posts: 69,630
Joined: 19-June 07
From: SunnySandyEggo


Hi,

See if this article on opening a text file helps.

However, instead of using For Input, you would use For Output and then use the Write statement instead of the Input statement.

Hope it helps...

--------------------
Go to the top of the page
 
alopez20
post Mar 21 2017, 02:23 PM
Post#7



Posts: 28
Joined: 28-November 12



I got it to append, but it's putting a text qualifier " around everything it appended into file1. I need it to be delimited comma no text qualifier, which is specified on export and its correct on export. not sure why it's adding " on append. Do you know how to fix?

DoCmd.TransferText acExportDelim, "CONS_FILE_Export Specification", _
"CONS_FILE_NR", UserInput & "\CONS_FILE_NR.csv", True

DoCmd.TransferText acExportDelim, "CONS_FILE_Export Specification", _
"CONS_FILE_R", UserInput & "\CONS_FILE_R.csv", True

' The file that contains the data to be copied over
Open UserInput & "\cons_file_r.csv" For Input As #2
' The file where the data will be appended
Open UserInput & "\CONS_FILE_nR.csv" For Append As #1
' Loop through the data in file number 2 (#2) ...
Do Until EOF(2)
Line Input #2, Data
' ... and append (write) the data into file number 1 (#1)
Write #1, Data
Loop
' Close the files
Close #2
Close #1
Go to the top of the page
 
theDBguy
post Mar 21 2017, 02:32 PM
Post#8


Access Wiki and Forums Moderator
Posts: 69,630
Joined: 19-June 07
From: SunnySandyEggo


Well, let's see. If you open CONS_FILE_NR.csv, does it have the quote (") delimiters towards the beginning rows? If so, I would double-check the export specification to make sure it has a comma as the designated delimiter. If not, and the only rows with quotes are those from the CONS_FILE_R.csv, then try using the Print # statement instead of Write #.

Hope it helps...

--------------------
Go to the top of the page
 
alopez20
post Mar 21 2017, 03:34 PM
Post#9



Posts: 28
Joined: 28-November 12



Print worked!! YAY Thanks!
Go to the top of the page
 
theDBguy
post Mar 21 2017, 03:39 PM
Post#10


Access Wiki and Forums Moderator
Posts: 69,630
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Congratulations! Glad to hear you got it to work. Good luck with your project.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th May 2017 - 04:48 AM