How to change headers?

[wharting]

Thanks rpeare.


I had to change the code because of more columns then I was talking about.
They spaces in some fields are there because they are the original fieldnames.
To avoid that I have to edit the CSV file before importing into a table; I don't see another solution.

Only the fields with 2011-x in the header changes every month. This month the downloaded CSV contains 2011-5, 2011-6, 2011-7, 2011-8. Next month 2011-6, 2011-7 and so on. The end of year column is 'fixed' and the amount is given and ain't a calculation within a query.

[rpeare]

The .csv file you enclosed is not a .csv file. CSV stands for comma separated value, these are actually semi colon delimited (. And each row starts with a " and ends with a " basically indicating to access that the entire row is one big text string. Is this actually how the data comes to you or are there no " marks in your incoming file?

If you remove those " marks you can create an import spec that will properly import the data (either name it something new or overwrite the spec I gave you). If your file does actually come with each line enclosed in " marks you may have to write an addition to the script I gave you that opens the file and removes the " marks it finds then saves it.

Secondly, your field on the end Estimated cost at the end of the year has commas in odd places for instance, these are the two values you have in your last field. Neither one of these is a number format, am I to assume the , (comma) mark is actually supposed to be a . (period)

12584,22042857
115,93741936

Third, you've modified and butchered the SQL statements. Some of your data has a ' (apostrophe) mark in it so you're going to have to modify the SQL statements further. Here is the code I'm running on the button now and it correctly adds all the information.

Dim sFileName As String
Dim db As Database
Dim rst As Recordset
Dim sSQL As String
Dim sMonthLabel1 As String
Dim sMonthLabel2 As String
Dim sMonthLabel3 As String
Dim sMonthLabel4 As String

If IsNull(lstFileName) Then
    MsgBox "Kies voor het importeren een bestand"
Else
    Set db = CurrentDb
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("DELETE * FROM tblRawImport")
    DoCmd.SetWarnings True
    sFileName = CurrentProject.Path & "\" & Me.lstFileName
    Debug.Print sFileName
    DoCmd.TransferText acImport, "ImportSpecNew", "tblRawImport", sFileName, False
    
    sSQL = "SELECT * FROM tblRawImport"
    Set rst = db.OpenRecordset(sSQL)
    rst.MoveFirst
    sMonthLabel1 = rst.Fields("FieldA")
    sMonthLabel2 = rst.Fields("FieldB")
    sMonthLabel3 = rst.Fields("FieldC")
    sMonthLabel4 = rst.Fields("FieldD")

    rst.MoveNext
    Do While rst.EOF <> True
        sSQL = "INSERT INTO tblImportedData (Company, Debtor, [Cost Type], MonthLabel, MonthAmount) VALUES "
        sSQL = sSQL & "(""" & rst.Fields("company") & """, """ & rst.Fields("debtor") & """, """ & rst.Fields("cost type") & """, """ & sMonthLabel1 & """," & rst.Fields(3) & ")"
        Debug.Print sSQL
        db.Execute sSQL
        sSQL = "INSERT INTO tblImportedData (Company, Debtor, [Cost Type], MonthLabel, MonthAmount) VALUES "
        sSQL = sSQL & "(""" & rst.Fields("company") & """, """ & rst.Fields("debtor") & """, """ & rst.Fields("cost type") & """, """ & sMonthLabel2 & """," & rst.Fields(4) & ")"
        Debug.Print sSQL
        db.Execute sSQL
        sSQL = "INSERT INTO tblImportedData (Company, Debtor, [Cost Type], MonthLabel, MonthAmount) VALUES "
        sSQL = sSQL & "(""" & rst.Fields("company") & """, """ & rst.Fields("debtor") & """, """ & rst.Fields("cost type") & """, """ & sMonthLabel3 & """," & rst.Fields(5) & ")"
        Debug.Print sSQL
        db.Execute sSQL
        sSQL = "INSERT INTO tblImportedData (Company, Debtor, [Cost Type], MonthLabel, MonthAmount) VALUES "
        sSQL = sSQL & "(""" & rst.Fields("company") & """, """ & rst.Fields("debtor") & """, """ & rst.Fields("cost type") & """, """ & sMonthLabel4 & """," & rst.Fields(6) & ")"
        Debug.Print sSQL
        db.Execute sSQL
        rst.MoveNext
    Loop

    Set db = Nothing
End If

NOTE I am NOT adding the last field to the table because, from the sound of it, it is a budget of sorts which should be on a separate table.

Lastly, you *STILL* have not said whether your data changes month to month

Take the example file you sent.

It covers months 2011-04 through 2011-07. the file you receive for 2011-08 will have the months 2011-05 through 2011-08. Will the fields 2011-05 through 2011-07 have the exact same numbers as the previous month or will they change. If they change do you want to overwrite the existing data? if they do not change do you want to only add the new month?

For instance

2011-04 2011-05 2011-06 2011-07
1000    1500    2000    2500

Let's say this is your data for the current file

Next month will you get this:

2011-05 2011-06 2011-07 2011-08
1500    2000    2500    3000

where the three first months are identical to the previous months last three months or can those values change month to month.

The method I've given you adds the data regardless of what happens. but you are going to have to modify the program based on what is actually the case with your system. If the data for the three overlapping months you have is going to be identical the easiest thing to do is to set your primary key to be the company, debtor, cost type and month label. This will prevent duplicates and will only add the new field (or you can modify the code to only add the most recent months' worth of data after your original import)

[wharting]

Hi rpeare,

I'm not be able jet to get along with it but want to react on your post.

>The .csv file you enclosed is not a .csv file. CSV stands for comma separated value, >these are actually semi colon delimited (.
We also use semicolons to separate the values.

>And each row starts with a " and ends with a " basically indicating to access that >the entire row is one big text string. Is this actually how the data comes to you or >are there no " marks in your incoming file?
No, the data comes without the " and shouldn't be there.

>Secondly, your field on the end Estimated cost at the end of the year has commas in >odd places for instance, these are the two values you have in your last field.
In the Netherlands we normally use a comma instead of a dot in fields with costs.

>Neither one of these is a number format, am I to assume the , (comma) mark is >actually supposed to be a . (period)
See remark above. The numbers behind the comma are decimals.


That,s why I had to butcher the SQL code; I have to make it compliant with other parts of the applcation.


BTW: I really highly appreciate your help; it's very valuable to me and learns me a lot.

Best regards,
Willem

[rpeare]

if your last field is always going to have a comma in it that's a problem and you will likely have to replace it with a . mark to make anything work. Because this is a 'budget' field (you said it doesn't change during the year) I would import it as a text value into it's own table then run a conversion, it's easier than dealing with changing all the incoming text files.

The SQL code you had was trying to add about 7 or 8 lines per file, not 4 lines, that's what I meant by butchered, plus, as I said, it was attempting to add 7 fields but only had 2 actual values to append.

And you still haven't answered my question about the files changing month to month but the code I gave above works. You just have to get your budget information into it's own table from here and follow my other remarks regarding setting a duplicate key for your month to month information based on my previous post.

[wharting]

if your last field is always going to have a comma in it that's a problem and you will likely have to replace it with a . mark to make anything work. Because this is a 'budget' field (you said it doesn't change during the year) I would import it as a text value into it's own table then run a conversion, it's easier than dealing with changing all the incoming text files.

The last field is a numeric field. I mend that the header doesn't change during the whole year. The value of it can change every month because it's a expectation of the year end costs. It depends of the costs in the other (month)fields.

And you still haven't answered my question about the files changing month to month but the code I gave above works.
“It covers months 2011-04 through 2011-07. the file you receive for 2011-08 will have the months 2011-05 through 2011-08. Will the fields 2011-05 through 2011-07 have the exact same numbers as the previous month or will they change. If they change do you want to overwrite the existing data? if they do not change do you want to only add the new month.”

The fields 2011-05 through 2011-07 will have the exact same numbers as the previous month. It’s not necessary to only add the new month. Because the ‘old’ fields contains the same numbers they can be overwritten.



Best Regards,
Willem

[rpeare]

Well you have the method, the only thing you have left to handle is the comma where a . should be in that final number and it sounds like you flush and refill this data each month instead of keeping a cumulative total.

[wharting]

I got it working

The issue with the ',' is an real issue indeed.

I only don't get the end of year costs into the db.
It's better to let it for what it is.

Another challenge now will be presenting the data in a pivotchart. Normally no problem but with changing headers a nice job.

[wharting]

BTW: many thanks for your help and patience

Reg.,
Willem