D said:
Hey guys- been searching for a while now, and all the posts are a little
more complex than I think this needs to be. Can you help?
Basically, I have a CSV file that I need to import into another 'Template'
file. But- here's the key- not EVERY column in the CSV file needs to import
into my template. Also, the columns in the CSV file are NOT in the same
order they need to be in the Template file. So, I need to be able to pick
and choose which columns import and go where. Does that make sense? Is there
an easy 'import mapping' system to use or any other way to do this? Like I
said, everything Im seeing is looking pretty complicated- more than I think
it needs to be. This seems as if it'd be fairly easy to accomplish, I'm just
not finding answers I need.
Thanks for any help.
D
Hello
Well I like programming/VBA approaches because its quick and gives you
good control. I dont know if there is any "Import wizard" that can do
this. To write code that does what you want is not very difficult.
A suggestion would be to read all of the CSV-file into an array and go
from there. It is easier and more effective to manupulate data "In
memory".
Something like this:
Function CSVFileToArray(inFile As String) As Variant
Dim fileHandle As Integer
Dim i As Integer
Dim outArray() As String
Dim tempStr As String
If Dir(inFile) = "" Then 'Check if the file exists
Msgbox "CSVFileToArray: Could not find/open " & inFile
Exit Function
End If
fileHandle = FreeFile()
Open inFile For Input As #fileHandle
i = 0
Do While Not EOF(fileHandle) ' Loop until end of file.
On Error Resume Next
Input #fileHandle, tempStr
On Error GoTo 0
If tempStr <> "" And InStr(",", tempStr) = 0 Then
ReDim Preserve outArray(i)
outArray(i) = tempStr
i = i + 1
End If
Loop
Close #fileHandle
CSVFileToArray = outArray
End Function
Then you can describe what you want from the file in another array
that you initialize early on. Here is an example function from a
program I've done:
Type tFieldDesc
sFieldName As String
iColPos As Integer
bConvert As Boolean
bDate As Boolean
End Type
Function CreateFieldDescrArray() As tFieldDesc()
Static bIsInitialized As Boolean
Static arrFieldDesc(FIELD_DESC_ARRAY_UBOUND) As tFieldDesc
'CAUTION the global constant FIELD_DESC_ARRAY_UBOUND must be set to
'the number of field descriptions minus one!
'An array that contains descriptions of fields
'0 = The name of the field
'1 = The column position
'2 = Does the field need conversion
'3 = Is it a date field
If Not bIsInitialized Then
Dim arrTmp, vElement
arrTmp = Array( _
Array("Provid", "1", "", ""), Array("Status", "2", "Y", ""), _
Array("UPPDRAG", "6", "", ""), Array("Header", "7", "", ""), _
Array("Bestnamn", "8", "", ""), Array("BestAvd", "9", "", ""), _
Array("Memoid", "10", "", ""), Array("UtfAvd", "11", "", ""), _
Array("StartW", "12", "", "Y"), Array("EndW", "13", "", "Y"), _
Dim i As Integer
i = 0
For Each vElement In arrTmp
arrFieldDesc(i).sFieldName = vElement(0)
arrFieldDesc(i).iColPos = CInt(vElement(1))
arrFieldDesc(i).bConvert = False
If vElement(2) <> "" Then
arrFieldDesc(i).bConvert = True
End If
arrFieldDesc(i).bDate = False
If vElement(3) <> "" Then
arrFieldDesc(i).bDate = True
End If
i = i + 1
Next
bIsInitialized = True
End If
CreateFieldDescrArray = arrFieldDesc
End Function
Then you write the main data crunching Sub. Something like this:
Sub GetTheData()
arrFieldDescr = CreateFieldDescrArray()
arrTheWholeFile = CSVFileToArray("C:\Datafile.csv")
j = 0
k = 0
For i = 0 to Ubound(arrTheWholeFile)
If j Mod NUMBER_OF_COLUMNS = 0 Then
'NUMBER_OF_COLUMNS is a constant that has the number of columns
in the
'CSVfile you wish to process
j = 0
else
j = j + 1
end if
sResult = GetElement(arrFieldDescr, arrTheWholeFile(i), j)
if sResult <> "" then
arrResult(k) = sResult
k = k + 1
end if
next
end sub
Function GetElement(arrFieldDescr, sElement, j) as string
GetElement = ""
For each item in arrFieldDescr
if item.iColPos = j then
GetElement = sElement
break
end if
next
end function
This was a very quick repsonse just to get you started along the way I
would reccomend. All variables should be declared of course.
Regards /Patrik
