Avatar of Gozreh
Gozreh
Flag 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
Microsoft Access

Avatar of undefined
Last Comment
Gozreh

8/22/2022 - Mon
Ganapathi

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.
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 ?
Rey Obrero (Capricorn1)

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ganapathi

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.
Scott McDaniel (EE MVE )

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).
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gozreh

ASKER
thanks for your help