Importing, grouping and sorting data from a CSV file

[ctiwari]

Hi

I'm trying to import data from a CSV file into an access database and then sort it by distance and grouped by id.

so my table within the database should ideally look something like,...

ID Distance

1 3
1 4.5
1 5
1 7

I tried using a SQL select statement, but i guess I am getting it wrong.

'create table and import data from csv file
Dim strCSV As String
strCSV _
= "Select id, distance, weight" _
& " INTO TABLENAME IN'" & FileMDB & "' " _
& " FROM " & nctablefileCSV & " SORT distance GROUP BY id"
conCSV.Execute(strCSV)
conCSV.Close()

Any suggestions will be most appreciated!

Thank you,

Chet

 

[SonOfEmidec1100]

The correct syntax for sort is Order By.In your results you dont show weight but you are selecting it.
I dont really understand you use of GROUP BY, your results show more than row for an id 1 and you are not selecting an aggregate.

As a start does this give you what you want, if not can you give an example of some input rows and expected output.

strCSV _
= "Select id, distance, weight" _
& " INTO TABLENAME IN'" & FileMDB & "' " _
& " FROM " & nctablefileCSV & " ORDER BY id,distance"

 

[ctiwari]

Wow! That was simple!
Thanks a ton.

- Chet