Archive

Archive for the ‘CSV’ Category

Combine csv’s into one Workbook

February 2nd, 2012 admin No comments

Lately I was looking into the issue of combining all csv files in a folder into a single workbook.
One could do this in vba but it needed to be a scheduled overnight process.
As I recently discovered PowerShell I decided to give it a fair chance.

This is the script I came up with and to be fair it works like a charm.
Some minor tweaks need to be done but I couldn’t resist the post because it has been a while.

$files = get-childitem 'D:\Data\'

$datafiles = $files | where {$_.extension -eq ".csv"}

$Excel = New-Object -Com Excel.Application

$Excel.ScreenUpdating = $false
$Excel.DisplayAlerts = $false

$WorkBook = $Excel.Workbooks.Open('D:\Data\'+$datafiles[0].name)

$mv = [System.Reflection.Missing]::Value

for ($index = 1; $index -lt $datafiles.Count; $index++)
{
 $WorkSheet = $WorkBook.Sheets.Add($mv,$mv,$mv,'D:\Data\'+$datafiles[$index].name)
 $WorkSheet.Cells.EntireColumn.AutoFit()
}

$Excel.Visible = $True
$Excel.DisplayAlerts = $True
$Excel.ScreenUpdating = $True
Categories: CSV, Excel, PowerShell Tags: , ,

Bottom left part of the UsedRange

June 6th, 2011 admin No comments

The csv file I import in my previous post contains two rows of informational data followed by a blank row followed by the real data. I needed to determine this range to loop though.
I decided to make a function and this is what I cam up with.

Feel free to shoot at it….

regards

Function DataRange(tmpWorkSheet As Worksheet) As Range

    Dim rTemp As Range

    Set rTemp = tmpWorkSheet.UsedRange.Columns(1)
    Set rTemp = rTemp.Cells(rTemp.Rows.Count, 1)
    Set rTemp = rTemp.CurrentRegion

    Set DataRange = rTemp

End Function
Categories: CSV, Excel, VBA Tags: , ,

Scheduled Import a online csv file into Workbook

May 27th, 2011 admin No comments

For a client I needed a solution for a scheduled import of a online csv file into a workbook. There are many ways to download files from the internet but it needed to be in excel as there were a whole lot of macro’s allready in place to transform and process the downloaded data. They had a machine running at all time ( not a server ) to be used.
So this is the code I came up with. It runs every day after it is started by running the StartSchedule Procedure

Option Explicit

Public tTime As Date

Sub StartSchedule()

    tTime = Now()
    Schedule

End Sub

Sub Schedule()

    tTime = tTime + TimeSerial(23, 59, 59)
    Application.OnTime tTime, "ImportCSV"

End Sub

Sub ImportCSV()

    Dim tmpSheet As Worksheet

    Set tmpSheet = ThisWorkbook.Sheets.Add(,,, "http://www.dummy.com/temp/file.csv")

    tmpSheet.Cells.EntireColumn.AutoFit
    tmpSheet.Name = tmpSheet.Name & " " & Format(Now(), "yyyy-mm-dd hh-mm-ss")

    Schedule

End Sub
 
Categories: CSV, Excel, VBA Tags: , ,

Pivot Table based on CSV file.

May 17th, 2011 admin No comments

July 31st, 2008

It’s been a while since my last post.
Due to my new job and rebuilding my house free time was scarce.
In my line of business we work a lot with data from numerous suppliers.
Some of the data is provided in csv files which can be imported in excel to base a pivot table on.
But what if the number of records / lines exceeds 65536 in excel 2003 or 1048576 in excel 2007.
You can cut the csv file in parts and base the pivot table on multiple consolidation ranges.
This was too much of a hassle for me and I started thinking about a more structured way to
create a pivot table on a csv file, with this result…
What is does is in fact simple but beautiful.
Select CSV File, create a recordset from it and attach it to the pivot cache to base a new
pivot table on this cache.

Option Explicit

'Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Const sConnStrP1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
Const sConnStrP2 = ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Const sFilter = "CSV File, *.csv"

Sub CreatePivotTableFromCSV()

  Dim sFileName As String
  Dim sFilePath As String

  sFileName = Application.GetOpenFilename(sFilter, 1, "Select File", , False)
  sFilePath = Left(sFileName, InStrRev(sFileName, "\"))
  sFileName = Replace(sFileName, sFilePath, "")

  TestCSV sFilePath, sFileName

End Sub
Sub TestCSV(ByVal sFilePath As String, ByVal sFileName As String)

  Dim cConnection As ADODB.Connection
  Dim rsRecordset As ADODB.Recordset
  Dim pcPivotCache As PivotCache
  Dim ptPivotTable As PivotTable
  Dim SQL As String

  Set cConnection = New ADODB.Connection
  cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

  SQL = "SELECT * FROM " & sFileName

  Set rsRecordset = New ADODB.Recordset
  Set rsRecordset = cConnection.Execute(SQL)

  'For Excel 2003 Use
  'Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
  'For Excel 2007 Use

  Set pcPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
  Set pcPivotCache.Recordset = rsRecordset
  Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=Range("B5"))

  cConnection.Close

  Set rsRecordset = Nothing
  Set cConnection = Nothing

End Sub

Cheers

Comments are welkom

Categories: CSV, Excel, Pivot table Tags: , ,