Free Microsoft Excel 2013 Quick Reference

[Solved] VBA: Concatenating .csv files

I have multiple .csv files that I need to Concatenate together into a new file. I tried using the following shell command but it does not work when trying to do this on files on a network drive.

'make sure we are pointing at the correct directory to concantinate the temp files into one.
'-------------------------------------------------------
ChDir PathToCSVFiles

'Concantinate the temp files into one file called "fileout.tmp.csv"
'-------------------------------------------------------
Shell ("c:winntsystem32cmd.exe /c copy /B *.tmp fileout.tmp.csv")

The shell command likes things very specific and does not wnat to take variable in the () in order to build the full command. Any simple suggestions?
The path to get the files and the output file path are the same.
Thx
K


Post your answer or comment

comments powered by Disqus
Hi all,
I'm having major problems opening a .csv file using a macro. When opened 'manually' through windows explorer the data is correct, however, when I run a simple macro to do the same thing, dates get swapped from dd/mm/yyyy to mm/dd/yyyy, but only in certain cells in the same column. this is not down to cell formatting, as all cell formats are set the same.. Any help would be MUCH appreciated!

Hi all,

Although I already found a lot of useful information on Ozgrid I could not find anything to solve or explain the following issue. So bare with me since this is my first post ! :-)

I have report which is based on CSV data in which there is a date colum.
When I manually open the CSV file and use text-to-columns all data is converted correctly.

To automate the report I recorded a macro for the same steps, open CSV file => Select data => Convert with text-to-columns and all data is correct.
When I run the macro however, the date column is formatted incorrectly, the dates in the CSV file are dd/mm/yyyy but when converted by the macro some (not all) are mm/dd/yyyy.

I searched the www for similar issues and tried all solutions I could find including adding the date format for the date column (Array(1, 4), the dates however are still incorrect.

I then did a debug of the macro (step-by-step via F8) and I noticed that when the CSV file is converted automatically when opened via the macro, so BEFORE the text-to-columns instruction is processed ! Which is the reason why the date column is formatted incorrectly and the Array format was not processed.

In the macro I use the Workbooks.Open instruction to open the CSV file but I also tried the Workbooks.Opentext instruction, both with the same result.

Does anyone have an idea why this happening when opening the CSV file via the macro but not when opening the same file manually ?

Any help is already much appreciated !

Phill

OK folks, I have a bit of a problem:

I have a spreadsheet, which extracts data from a csv file if the submission date is between a start date and an end date.

Now let's say the csv file contains a data item with a submission date of 6 September 2008. Now if I open the csv file manually, the formatting is correct (UK style - dd/mm/yyyy hh:mm) - I can prove this by adding e.g. 1 to the date and verifying that it now equals 7 September.

However, when my VB code opens the CSV file, it opens with the date formatting still correct (i.e. UK style) - but it now takes the date to be 9 June 2008 - again can be verified by adding one to the date and getting 10 June 2008.

I have come across UK/US date formatting problems in the past, and usually "Cdate" solves the problem. However here, this doesn't solve the problem, because "Cdate" will only work if the raw data is correct (i.e. it is just a us/uk formatting problem). The problem is that when VB opens the CSV file, it is actually changing the data - rather than the formatting.

If anyone wants to have a look at this, I am calling the csv file with a line of code:

Code:
I have uploaded the CSV file here, so if you can give it a try:
https://www.yousendit.com/download/b...MGszeUtGa1E9PQ

Any ideas?

The only solution would be for me to manually open the report, format the column as numbers, and then convert to date, save the csv and then call it with VB - but this is not very elegant - and a pain in the neck.

Thanks.
Gopes

Hi Friends,

Need some help on below, i am new to VBA programming & i don't know how to get this correct.I tried myself since last 3/4 days but not resloved yet.
I have one excel file contains 3 sheets, outof that 1 sheets contains the data which is linked to others 2 sheets. Data comes from csv file. I want to import the lastest csv file from specific location into same sheet on daily basis (overwriting the exisiting data). Here is my code.


	VB:
	
 unhide() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Sheets 
        If ws.Name = "DATA" Then 
            ws.Visible = xlSheetVisible 
        End If 
    Next ws 
End Sub 
 
Sub DeleteRows() 
    Dim ws As Worksheet, lRow As Long 
    Set ws = Worksheets("DATA") 
    lRow = ws.Range("A65536").End(xlUp).Row 
    ws.Rows("1:" & lRow).Delete Shift:=xlUp 
    Set ws = Nothing 
End Sub 
 
Sub Home() 
    Application.Goto Sheets("DATA").Range("A1") 
    Application.SendKeys ("^{Home}") 
End Sub 
 
Sub LoadFromFile() 
    Dim fileName As String, folder As String 
    folder = "D:tempSummary_Report_*.csv" '=>Note (* means date)
    ActiveCell.Offset(0, 0).Range("A1").Select 
    With ActiveSheet.QueryTables _ 
        .Add(Connection:="TEXT;" & folder, Destination:=ActiveCell) 
        .Parent.Name = "DATA" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = True 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 850 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = False 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = True 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 1, 1, 1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 
 
Sub hide() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Sheets 
        If ws.Name = "DATA" Then 
            ws.Visible = xlSheetHidden 
        End If 
    Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have attached a Sample.xls. Please assist.

Hey guys. I am currently close to completing a football sim engine, run on excel. I think I need some VBAs, to export a csv file that can be uploaded, to a website, that i can download and import into my sim engine. (I hope that makes sense )

Basic logic is easy for me to understand (excel) but VBAs fry my brain.

anyway, I would like some help on making a VBA thatdoes the following:
*exports to csv
*names the file the text I have on sheet1!a1
*saves the file in the same directory that the opened file is saved at.

and if possible, code for appending the file (don't know if this is possible). Thanks guys, I spent a good 3 hours looking for help on vba coding and I can't seem to comprehend it . thanks again.

Hi,
Looking for VBA code that externally links CSV files for MSAccess. Thanks!!

I need to import CSV files that contain over a million entries and importing them using the code below works but the database file becomes very large and the limit is 2. something gb.
DoCmd.TransferSpreadsheet acImport, 8, "MeterFileTbl", MeterFilePath, True, "A1:C2"

I have the code for linking XLS files but can't find the VBA code for the CSV format... Thanks in advance for your help!


	VB:
	
 
Set myDB = CurrentDb() 
stSource = "Sheet1$" 
 'stConnect = "Excel 5.0;HDR=YES;IMEX=1;Extended Properties=text;FMT=Delimited; Database=C:Documents and
Settings202968DesktopMySheet.csv"
 
Set tbl = myDB.CreateTableDef("mySheet") 
tbl.Connect = stConnect 
tbl.SourceTableName = stSource 
myDB.TableDefs.Append tbl 
LinkExcel = True 
Exit_: 
Set tbl = Nothing 
Set myDB = Nothing 
Exit Function 
Err_: 
LinkExcel = False 
MsgBox Err.Description 
Resume Exit_ 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have two CSV files that I would like to edit through Excel VBA.

Both files have too many lines to be shown in Excel though, so I can't just use cell rerences.

Every so many lines I want to delete a row and insert a row from one CSV file to the other.

I could easily write a loop do this with cell references, but since all the lines aren't shown I can't do that.

I have no idea how to reference all the rows in the CSV file with VBA when most of them aren't viewable on the worksheet.

Any references or thoughts would be appreciated!

Hi everyone,

My question is a little odd, but I'll try to portray it to make sense. Basically, I want the user to be able to click a "Browse..." button, choose a file, and import it into an Access table. Now, I've got this part down pact for Excel files, but I'm having a problem with the CSV file types. Is there a way that when the user selects a CSV file, code can be written to save it as an Excel file through VBA and import that instead of the CSV file. Ideally, I'd like it to be able to do that and then delete the Excel file it made for the import purpose. I know I can use the TransferText method, but its giving me problems because I only need a certain range fro the CSV files. Any ideas?

Let me know if this needs more clarification.

-beveritt

Hi,
i need procedure in VBA to import data into access from csv excel file without some records,, as header and footer. Example,,, i have table in csv file, which contains some
sentence which not belong table date
_______________________________________
A1 this is some sentence title.......
A2 title
A3.......
A7 DATA DATA DATA DATA DATA
A8 DATA DATA DATA DATA DATA
......
....
A256 DATA DATA DATA DATA
A257 this is some sentence
________________________________________________

My Acess shoud contain only record between A7 to A256. Does anyone knows procedure or whatever in VBA who solves my problems ?

thanks a lot

Basically I have a data sheet with some columns containing numbers some containing dates.

And I need to change the fields so they appear with " around them, then this needs to be converted and saved as a .csv file.

I can achieve this by creating 3 different sheets, then using =TEXT(N2,"dd/mm/yyyy") on the date fields. Then finally using =CONCATENATE("""",Sheet1!A2,"""") to create the the desired effect. I was just wondering if there was some sort of quicker way to do this?

Another probelm I have found with this is that when opening the .csv file in Notepad it display's 3 "'s instead of the original 1 that I am after.

Does anyone have a solution to this?

Hi all

I don't have a great deal of experience with VBA, but can usually muddle my way through. I have drawn a blank with the following, however.

I have a folder containing multiple excel workbooks, all of which are of the same structure. The workbooks contain multiple work sheets. I want to write a macro that saves each worksheet within each workbook as a separate .csv file that is named "NameofWorkBook_NameofWorkSheet". These would all be saved into the existing folder.

I have been reading threads on similar topics and trying to meld them together, but the best I have been able to do is save each worksheet within a single workbook as the name of the worksheet. I am using Excel 2010. Any assistance with sample code, or directing me to where this has previously been addressed would be most appreciated.

Thanks in advance...

Hi All,

Through my Excel VBA i want to
1. Open a csv file
2. Read each row
3. Select few fields
4. Format them
5. Concatenate them as a single text separated by "#"

and

6. Finally write the output into a text file

I know the name and location of the input csv file and therefore I
don't want the user to open it manually. Rather the file should be
opened automatically using VBA code for reading.

Some suggestions and code examples please.

Thanks & Regards,
Prasad

Hi Ozgriders, I need to export values directly from a user-defined array variable to a csv-file (without publishing array-values into a worksheet) . The code supplied below needs adjustments to allow this. After these adjustments, the desired output from this VBA-code would be:

1st row in CSV File has field-headings: “DateX”,"Number1”, ”Text1” in accordance with the names of the components of the user-defined array-variable (nb: component-names will vary, hence the need for a VBA script to automate this)Subsequent rows in the CSV file contain values from the array-variableNote: when exporting, I need to distinguish whether the data is a string (to be enclosed by quotes) or numeric or date (not to be enclosed by quotes). I’ve used the Val() expression here but it doesn’t work for the date (when exported, it’s enclosed by # symbol). I need the date-values published into the CSV file as xx/xx/xxxx format & not enclosed by quotes.Many thanks - code follows (changes are needed for the “ExportRange” procedure, first two are for information. Regards, Peter.


	VB:
	
 
 
Type sample_type 
    DateX As Date 
    Number1 As Long 
    Text1 As String 
End Type 
 
Public myArray(1 To 3) As sample_type 
 
Sub Load_values_2_myArray() 
    Dim i As Long 
    For i = 1 To 3 
        With myArray(i) 
            .DateX = Date + i 
            .Number1 = Rnd() 
            .Text1 = "blah" & i 
        End With 
    Next i 
End Sub 
 
Sub Export() 
     'Ozgidders: This procedure is setup to fetch values from a selected range
     'What changes are needed to make it fetch values from the above user-defined
     'array and:
     
     '(1)save the array component-names as the field-names in the 1st line of the CSV file
     '(2)publish the array values in the remaining lines of the CSV file?
     'alternative code to below is welcomed.
     
    Dim Filename As String 
    Dim NumRows As Long, NumCols As Integer 
    Dim r As Long, c As Integer 
    Dim Data 
    Dim ExpRng As Range 
    Set ExpRng = Application.Intersect(Selection, ActiveSheet.UsedRange) 
    NumCols = ExpRng.Columns.Count 
    NumRows = ExpRng.Rows.Count 
    Filename = "C:" & "textfile.csv" 
    Open Filename For Output As #1 
    For r = 1 To NumRows 
        For c = 1 To NumCols 
            Data = ExpRng.Cells(r, c).Value 
            If IsNumeric(Data) Then Data = Val(Data) 
            If IsEmpty(ExpRng.Cells(r, c)) Then Data = "" 
            If c  NumCols Then 
                Write #1, Data; 
            Else 
                Write #1, Data 
            End If 
        Next c 
    Next r 
    Close #1 
    MsgBox ExpRng.Count & " cells were exported to " & Filename, vbInformation 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, everyone,

I am trying to develop a VBA code to have the user select the csv file that will be linked to the pivot table.

I have this code so far. It still does not change the source file. The GetFileName variable in the SQL "From" statement is obtained through another code (not provided here) and is a name of the file with extension ("4 Port.csv" in my case). Filename is the full path to that file.

Please advise.

Sub
ChangeData()
    
    SelectFileToOpen
    
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = Array(Array( _
        "ODBC;DefaultDir=" & Filename & ";Driver={Microsoft Text Driver (*.txt;
*.csv)};DriverId=27;FIL=text;MaxBufferS" _
        ), Array( _
        "ize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
        ))
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT `4 Port`.`Date;Time`, `4 Port`.`Part Number`, `4 Port`.`Body Cavity`, `4 Port`.Passed, `4 Port`.`Julian
Date`, `4 Port`.`Test Version`, `4 Port`.`Flow Code`, `4 Port`.`Mass 1`, `4 Port`.`Mass 2" _
        , _
        "`, `4 Port`.`Mass 3`, `4 Port`.`Mass 4`, `4 Port`.`mm^3/Shot 1`, `4 Port`.`mm^3/Shot 2`, `4 Port`.`mm^3/Shot
3`, `4 Port`.`mm^3/Shot 4`" & Chr(13) & "" & Chr(10) & "FROM " & GetFileName
& Chr(13) & "" & Chr(10) & "ORDER BY `4 Port`.`Date;Time`")
    End With


End Sub
This question has been asked here http://www.mrexcel.com/forum/showthr...63#post3050763 too but only partial answer (thanks for that anyway) received.

Hi,
I am considering developing a VBA project within Excel which performs some analysis on a .csv file and then creates some reports.

The idea is for my VBA code to create a multi-dimensional array from the .csv data and then create reports from the array.

The .csv file could be as large as 500,000 (five hundred thousand) rows long and have approx. 15 columns.

I realise that I am giving very little information but as yet I don't know it all (haven't been given all of the information myself yet). However, please may I ask advice on the following.....

1) What would be a rough estimate for the filesize of a 500,000 line, 15 column .csv file?

2) How fast is VBA?
i.e. If my code read each row of the .csv file, performed a simple calculation on the row's data (such as adding each of the 15 cells' values together) and stored this in the multi-dimensional array how long might it take to complete 500,000 rows?

Generating the report(s) can come afterwards and I suspect will be much quicker once the data has been collated into the array.

Once again, sorry I can't give any more information at this stage. I just wanted some idea of whether the job would be feasible in the first instance.
500,000 rows is an aweful lot and I don't want customers having to wait a long time for a result

Many thanks for your time

Hi Guys,

I have written a simple Perl script to divide a very large txt file into a large number of smaller csv files. I have to add some formatting and VBA code to the CSV files though (the formatting and the VBA code is the same for all the CSV files) and I would like to add it via the Perl script. Is there a way I can do this automatically ? Can I add the formatting and the VBA code in the CSV files ?

Thx,
Turbo75

i think i put this in the wrong thread, so i have put it here.

I have a vba script that captures data from certain rows and columns and pushed the output to a csv file.
I am having problems creating an and/if statement to push data from one cell and combine it with data from another cell to the output.

I need to do a test where if data in Column B (vba script RC[-4]) = 1, then combine the data from Column E (vba script RC[-1]) to now read DP202876, else if RC-4 is greater than 1 then SP202876.

thanks in advance

Hi All,

Through my Excel VBA i want to
1. Open a csv file
2. Read each row
3. Select few fields
4. Format them
5. Concatenate them as a single text separated by "#"

and

6. Finally write the output into a text file

I know the name and location of the input csv file and therefore I
don't want the user to open it manually. Rather the file should be
opened automatically using VBA code for reading.

Some suggestions and code examples please.

Thanks & Regards,
Prasad

Hi all,

As a cell biology researcher I am not too familiar with VBA and programming in general, so please bear in mind I'm a beginner. After spending quite some time trying to find an online solution to my problem on various message boards, I decided to join up and share my challenge with you guys.

The problem is as follows: I have a cell stimulation setup that produces a small csv file every ~150 seconds (while experiments with this setup can last for days...). This file contains 6 columns, 4 of which describe measured values in different channels. From each file I'd like to extract the average signal (rows 30-80) minus the average background (rows 90-109) for each of the four channels (columns C-F). Signal minus background *1000/0.5 gives me the electrical resistance of my stimulated cells.

Making use of different bits of code found online and typed by yours truly, I compiled the following macro:

Sub
Data_extractor()

Application.ScreenUpdating = False

Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "D:Data*.csv"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop

Set r = Range("A1")
Dim rowx As Long
rowx = 1
While r.Value <> ""
Workbooks.Open Filename:="D:Data" & r.Value
    
    Range("C3").FormulaR1C1 = "=AVERAGE(R[7]C:R[78]C)"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[86]C:R[105]C)"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C-R[-1]C"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C/0.5*1000"
    Range("C6").Select
    ActiveWindow.SmallScroll Down:=-8
    Range("C3:C6").Select
    Selection.AutoFill Destination:=Range("C3:F6"), Type:=xlFillDefault
    Range("C6:F6").Select
    Selection.Copy

Range(Cells(rowx, 2), Cells(rowx, 5)).Select
Range(r, 2).PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Set r = r.Offset(1, 0)
rowx = rowx + 1
Wend

Application.ScreenUpdating = True

End Sub
What I want it to do is list the filenames of the csv files in column A, and resistances for channels 1-4 in columns B-E next to the name of the file they're derived from. The code as given above, however, only results in a 'runtime error 1004: application-defined or object-defined error'. All suggestions and improvements are welcome, since manual processing of these files is not exactly an option

Thanks in advance for your help!

Dirk-Jan

PS: Since I wasn't allowed to upload csv files I converted a sample file to xls before uploading it to give you an idea of the data involved.

I'm looking for a way to import multiple CSV files from a chosen folder and add them as new worksheets at the end of an existing workbook (I'm using Excel 2000 for this). I've actually found a few scripts here and elsewhere that nearly accomplish this, but I'm new to VBA and haven't managed to tweak them successfully.

http://excel.tips.net/Pages/T003148_..._Workbook.html

The script referenced above works well, but it creates a new workbook for the copied sheets. Ideally I want them to be added at the end of the existing workbook and I'm not sure how to change this.

Also, can anyone recommend any good tutorials to get started with VBA in Excel? I've done various bits of scripting in the past but never with any MS languages. Thanks in advance, if any more details are required please let me know.

Hi there,

I need to save multiple worksheets in an excel file to individual CSV files. Right now, I am doing this manually once a week, but since there are over 30 worksheets, this is obviously quite cumbersome.
Attached is an example of the logic.

I have 2 "master sheets" in the workbook that do the heavy lifting of the analysis, populating the 30 worksheets with updated data, once I ran my update links etc. What I would need is some sort of VBA code that can easily save each worksheet as a CSV file in the same folder that the master file resides in, using the worksheet name as the file name, requiring no human interaction.

Do you think that is possible?

All worksheets that need to be exported start with the word "CSV", so that could be an automatic criteria for the macro to understand which sheets need to get exported.... I couldn't find a similar solution in the forum unfortunately, which struck me as weird, as I don't believe to have such a unique requirement. If there's anything out there already that does what I need, please post the link, it's very much appreciated!

Best regards,
Titus

Hello.

Welcome every one.
I have some data in CSV

I need to open this CSV file via VBS script and add some data in the end of first line and save this file. For example

aaa;bbb;ccc
111;111;111
222;222;222

should be:

aaa;bbb;ccc;ddd;eee;fff
111;111;111
222;222;222

Hot to do it with VBS ? (not VBA)

Best Regards
C

Hello everyone. I am new to forums so I am unsure if I have posted this in the correct place. I really need some help writing a macro but I don’t know anyone who would be able to help me with this.

I currently have to produce weekly performance reports for the company that I work for. I receive a CSV file every week that lists around 100 processes that are undertaken by 9 different teams.

I have to sort out the data and produce the weekly workload reports using a standard format. This is very repetitive and I was wondering if it would be possible to write a macro that could produce the report for me (or at least automate some of this process for me). I use VBA to create some very basic userforms and navigation buttons but I have never tried anything this ambitious. I think that the main problem is that the number of processes each week can vary, as sometimes processes are replaced with others. Maybe I could use autofilter and copy sections of the file CSV file.

I have attached a file that shows the format of the CSV file I receive and the report presented in the specified format that I am expected to produce. I can’t really be very descriptive about the processes as I would probably get into trouble. The report groups the processes undertaken by each team and calculates totals with some conditional formatting. It is quite difficult to explain but the file shows the transformation from the raw CSV file.

Any advice would be greatly appreciated. I understand that I haven’t explained this very well so if there is anything you need me to clarify, let me know.

A software on my pc collects various csv files from several other pc's on the plant. There are 3 different files.

What I would like to do is to run a macro every day which opens that software, import all the csv files to an excel 2007 spreadsheet, add the specific header to each file ( I have 3 different headers in one excel file, one for each type of file) and then save the files in xls format.

Could someone provide me with the VBA code for this please?


No luck finding an answer? You could always try Google.