Link to home
Start Free TrialLog in
Avatar of Gozreh
GozrehFlag for United States of America

asked on

Importing csv file - duplicate field names

I want to import .csv files to my application using  DoCmd.TransferText acImportDelim, , FileName, MyFile, True
But getting an error "Cannot define field more than once"
So how can i check the field names before importing and change it, or any other solution.

Thanks
Avatar of Ganapathi
Ganapathi
Flag of India image

You have more than 1 Columns with same name in your CSV file. Identify and change the column names so that they look different.

Then try importing.
Avatar of Gozreh

ASKER

but this is the file i'm downloading from my vendor, how can i add some number to the field name before importing ?
if you are looking for a way to find the same column name using vba codes

Sub readCSVHeader()
Dim csvFile As String, fldArr() As String, j As Integer, s As String
csvFile = CurrentProject.Path & "\yourCsv.csv"
Open csvFile For Input As #1
Line Input #1, s
fldArr = Split(s, ",")
For j = 0 To UBound(fldArr)
     Debug.Print fldArr(j)
Next

Close #1

End Sub

the question is, why do you think there are duplicated column names?
where and how was the csv created?
See, the CSV file contains duplicate columns some or the other way. Either you will have to change it and load your way or the is invalid file from your vendor.

Check with your vendor on why they sent a file with duplicate columns.
If you review the file using Notepad, is there actually duplicate field names? You've indicated that the first row of your incoming file holds the Field Names, so check that first.

Otherwise, you'd have to open the file via code and example each value in that first row to determine what you should do. You can do that with code like this:

Dim fso As Object
Dim TextFile As Object
Dim DataLine As String


  Set fso = CreateObject("Scripting.FileSystemObject")
  Set TextFile = fso.OpenTextFile("H:\Development\Exact\Classic Turning\TestFile.txt", 1)
  DataLine = TextFile.readline

Open in new window

The variable "DataLine" would hold the first row of your text file, and you could then use Split and a loop to determine if there are duplicates:

  Dim s() As String
  Dim c() As String
  
  Dim i As Integer
  Dim j As Integer
  Dim cnt As Integer
  
  s = Split(DataLine, ",")
  c = Split(DataLine, ",")
  
  For i = 0 To UBound(s)
    cnt = 0
    For j = 0 To UBound(c)
     If Trim(c(j)) = Trim(s(i)) Then
        cnt = cnt + 1
        If cnt > 1 Then
          MsgBox "Duplicate Field Headers"
          Exit Sub
        End If
      End If
    Next j
  Next i

Open in new window

One thing to note: You should always move data into a "staging" table in your database, and then validate that data before moving it into your live tables. I speak from experience (and a veeeeery long weekend of data cleanup due to my not following this simple policy).
Avatar of Gozreh

ASKER

I'm getting this csv files from shipping vendors like DHL, TNT, FEDEX..... etc

here is a sample from one header row "Account Number,Invoice Number,Invoice Date,Payment Due Date,Delivery Company Name,Delivery Contact 1,Delivery Address 1,Delivery Address 3,Delivery State/ Province,Delivery Country,Delivery Post Code,Company Name,Contact 1,Address 1,State / Province,Post Code,Country,Consignment Number,Customer Reference,Child Account Number,Pick Up Date,Product Description,Billed Weight,Total Pieces,Division and Product codes,Ancillary Charges,Ancillary Code,Ancillary Description,Net Amount (FRT),Charge ID (FRT),Description (FRT),Declared Value (FRT),Net Amount,Charge ID,Description,Declared Value,Charge Element VAT Percentage 1,Net Amount,Charge ID,Description,Declared Value,Charge Element VAT Percentage 1,Net Amount,Charge ID,Description,Declared Value,Charge Element VAT Percentage 1,Net Amount (VAT),Charge ID (VAT),Description (VAT),Declared Value (VAT),Consignment Net Value (excl. VAT),Gross Amount,Discount Amount"

so as you see the last few names are the same, and the reason is for every type of charge they are using same field names

so now, thanks for all your help to get the first header line of the file, but my question is how can add some number to that headers and then importing it with the new headers ?

thanks for your help
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gozreh

ASKER

thanks for your help