Open and update Excel Workbook and saveas Question

[CJ_London]

looks to me like your error message does not include the .csv. if it does then chances are the length of path plus file name is too long. Pretty sure that has been mentioned before

your specification is using a semi colon as a field separator, not a comma (which is what a csv is - Comma Separated Values). If you actually do have semi colons and not comma's (we have yet to see an example of your file), then the usual way round that is to use a schema.ini file (at least for me).

it is unlikely the Umsatztest field will import as a datetime since it is not a valid date format for access. You need to do some transformation to exclude the part after the time element if you want it as a date

It would appear you don't try the things suggested, or if you do, you don't provide proper feedback, but go off in a different direction. So unless you can provide a sample of the .csv file (not one that has been opened using excel) and the details of your destination table, I'm going to drop out since all I am doing is repeating myself.

It's not difficult, open the file in notepad, leave the headers and 2 or 3 rows and delete the rest. You say some files have text qualifiers, others do not - prove it, provide an example of both files.

It imports but get an import error for some of the Date Values

go back into the file, (using notepad), find a row and compare the record with the ones around it, see what is different

 

[silentwolf]

Thanks guys,
@CJ_London
In germany and austria for some reason are csv files seperated as a semicolom(";")

@ebs17
I will provide a sample file but I am again out for work and will be back in few days
then I hope things are getting clearer.

Sorry for it to take some time but I am trying for what you guys suggesting.


Many thanks for your pacentice .

Wish you all a great day )

Cheers

 

[ebs17]

which is what a csv is - Comma Separated Values

In German regional settings, rather in all European regional settings with the exception of the islands of Great Britain, the comma is the decimal separator for numbers. Therefore, it cannot be used in parallel as a column separator. Welcome to the non-American world.

 

[CJ_London]

OK, my last word on this.

create a schema.ini file to go into the same folder as the file(s) you are importing. A schema.ini file is a bit like the import specification you are trying to create.

the file would contain the following two lines

[ImportFile.csv]
Format=Delimited(;)

As mentioned way back when, I copy the original file to a standard import name (ImportFile.csv in this example) overwriting whatever was there before, before importing so the ini file does not need to be recreated for each file

if you want to do more - specifying things like field types, etc, you can. See this link

Schema.ini File (Text File Driver) - Open Database Connectivity (ODBC)

Schema.ini File (Text File Driver)

learn.microsoft.com

however in my experience it is generally easier to handle that as part of the import process. By specifying hdr=no, that guarantees everything is brought through as text which can easily be converted to numbers/dates etc as required.

 

[CJ_London]

Welcome to the non-American world.

in the UK, if it is not a comma but semi colon or tab delimited, they are usually designated .txt. Hey Ho

 

[ebs17]

In an international and creative world there are all of them, including "comma separated values" files without any separations via delimiter. You just have to see what you are given.

It is interesting to add another variant (schema.ini) with its own special features to the variants of normal import by specification and saved import, in addition to the detour via Excel or through row traversal. Anyone who hasn't understood the BEFORE and mixes elements from it will win a few more elements for the lottery drum.

 

[CJ_London]

I avoid the specification route because they can get 'lost' when distributing FE's or they need updating remotely. I know you can use sql to create them providing at least one specification has been created to create the tables and set some properties. I guess those properties can be created via vba but I've never investigated as I find the schema.ini route easier and more controllable.

 

[ebs17]

You can handle it, I can handle both options. We can also manage to read values sequentially from the text file and save them in a table if necessary.
However, a discussion about this will not help Albert at this point.

First of all, the text files used must be analyzed to see which catalogs of atrocities are collected there, or not, because everything is neatly standardized.
I take it for granted that the developer knows and understands the table definition of the target table. So it should be clear to the developer how data must arrive.

 

[spaLOGICng]

Hi guys,
thanks for your replies!
@ebs17


yes it is.

The Bank Statements have for the first line of content always a certain structure.
IBAN; Auszugsnummer; Buchungsdatum; Valutadatum; Umsatzzeit; Zahlungsreferenz; Waehrung; Betrag; Buchungstext; Umsatztext

You are right that maybe if someone has modified that file in any way there could be an issue.
But it is is from the original Download so to speak than the file is in the same structure.

In the Column of Buchungsdatum I would like to find lowest Date and Highes Date, currently Code for workbookopen and looping through the Column,
As you guys mentioned that is not the savest way I was trying to do that with the InputStatement, but still working on it and not just yet able to do so.

My concept was open workbook and check for the first line of content. Check for the Account which is the last five digits of the IBAN,
Check for the lowestDate and highestDate in Buchungsdatum, and the Year of that file again from either the lowest or highest Date Value in Buchungsdatum.

Build a filename according to that information and save it to "MyFiles/2023/12345/12345_01To10_2023.csv" for example.

Once that has been done.
Import the files with transferText into my Database into Table IMPORT.

ImportSpecifications for that matter have been created and are able to work once the fileName is someting like 2023_01_09_12345.csv
or 12345_2023_01_09.csv or which ever sequence it would be.

If a file has been renamed and is saves already with a different name. I would like to just view the file first and if it is valid file then do the above and save it to the right location and the right file name.

@spaLOGICng

Ok that is great but again there still remains the issue of getting the fileName I like to get out of the File content of the IBAN " & "Dates

@CJ_London

I looked at the imput function
Currently just able to do the first line of content with code but not to sure how to get the dates out of it

Sorry for the delay.

Making sure the File Path exists before doing anything else is necessary,

You can add the FileSystemObject to you project. Tools > References > Microsoft Scripting Host Object ...


FolderExists method (Visual Basic for Applications) | Microsoft Learn