12/19/2018 0 Comments Microsoft Access Flat File Import WebI have a Microsoft Access file, and in it there is a lot of VBA code. I would like to collaborate with this code on GitHub. Unfortunately, the VBA code is embedded within the MS Access database and not as separate files. Hi, Background: I currently import CSV files using DoCmd.TransferText acImportDelim,, 'test', 'C: Users Ryan Desktop test.csv', 1. And this works. However, I am tired of getting the MS Access error that basically says 'somewhere in your import of 5,000 records there is a problem' without any further explanation. So I am ready to write my own CSV import. Question: Do anyone know where there is some code that will do the basics of importing a csv file such as: • Open the csv file • Read in each line of the csv file • Parse the line • Massage the data. (I realize that I have to write this section of the code.) • write out the resulting data. The incoming csv could be in either of the below formats: • first field, second field, 123, text, 1234 • 'first field with comma, so comma is in double quotes', 123, text, 124, 'more text' • 'first field with comma, so comma is in double quotes', '123','text','123,'more text' I just hate to re-invent the wheel and debug all of the issues that other have already figures out. If you don't know of sample code, can you please tell me what issues you have run into? Microsoft Access Flat File Import WebcamThanks, Dennis Solis. • Parse the line • Massage the data. (I realize that I have to write this section of the code.) The process seems pretty clear to me: 1. Read 1 line of Text at a time, storing it into a String Variable strLine. Inspect the first character of strLine. If it is a double-quote then find the position of the NEXT double-quote in strLine using InStr() function and extract the first Text Field value using Mid() function. If it is not a double-quote, find the position of the NEXT comma and extract the first Text Field value using Left() or Mid() function. (Optional) After step 2, change the value of strLine using Mid() to the sub String from the first comma after the FIRST Field value extracted to the end. While you can manage the subsequent steps without changing the value of strLine, it may be easier to think and code with the shortened value. Find the NEXT comma indicating the end of the second Field value (numeric) and extract this Field value. Repeat the above steps as appropriate for the subsequent Field values, i.e. For an expected Text Field, check whether the first character is a double-quote or not. Basically, I think the whole process is the repetitive but logical use of the 3 inbuilt String functions InStr(), Left() and Mid(). Hi Dennis, >>However, I am tired of getting the MS Access error that basically says 'somewhere in your import of 5,000 records there is a problem' without any further explanation.>So I am ready to write my own CSV import. Cara membuka password administrator windows 7 tanpa software. Fei, I understand that the data source is invalid. The question is, which of the 17 fields in the 5,000 rows is bad? MS Access simply says data field is bad and that is it. If I manually insert the table using an Insert query, I get the message '1 field was set to null', but no indication of which field or which row. So I am writing a program to import the data. My version of the import will tell me which field on which row has the issue so I can fix the data. Thanks for the articles, I will read them and see if I can figure out what they are saying. Thanks, Dennis Dennis Solis. Do anyone know where there is some code that will do the basics of importing a csv file such as: • Open the csv file • Read in each line of the csv file • Parse the line • Massage the data. (I realize that I have to write this section of the code.) • write out the resulting data. Hi Dennis, The points 1, 2 and 3 are pretty standard, Deric has given you an example. Point 4 is the unknown part, and is completely dependent on the typical data. Point 5 is dependent on your data structure, but can be structured quite a lot. Points 1 to 3 can be condensed in one function, with the file name as input parameter, and returning the next line until EOF, including all error handling. For these reasons I never use the Access function to import, but only use my own procedures. In your data example you can easily massage the data by removing the preceding and trailing doublequote. Imb, • yes, I agree thta 1, 2, and 3 an pretty standard. • I was able to read the csv file using the above code. • I am looking for sample code to parse the data record. The Split command does not work because it does not take into account data that has embedded comma within the double quotes. • I don't understand that removing preceeding and trailing quote do. This puts me back where I can not tell an comma in the data string from a comma as a data separator. • I can not find any comment added by Deric, so I don't see the example he provided. • I know that point 4 is code I have to write - hence the comment '(I realize I have to write this section of the code). Thanks, Dennis Solis. Deric, Please read comment # 2 and 3. The Split will not work because the following line will not be parsed correctly: 'this phrase, will not parse correctly, using this code', 'It should parse to two line, but will parse to 5 lines' It should be parsed as: • fld 1 = this phrase, will not parse correctly, using this code • fld 2 = It should be parsed to two lines, but will be parsed to 5 lines. However, it will be parsed as: • 'this phrase • will not parse correctly • using this code' • 'it should be parsed to two lines • but will be pased to 5 lines' So that is why the Split function will not work in this case. The second example that is provide is for Excel. Secondly, I can not use an automated function that just imports data as I need to clean the data after I read it in as a string and before I convert that data to a number, and then write that number out to a number field. I am currently using an MS Access automated process and it give me the error message 'Invalid data type'. Which would be ok if you were importing a single row. But when you are importing 5,000 rows at a time with 20 fields and MS Access gives you an error message saying 'Some fields in some row is bad - so MS Access will not import anything'. I need to be able to find the bad field in the data or at least in which row the bad data is in. Thanks, Dennis Solis. I would use the Microsoft Runtime Scripting Library and call the File System Object and INPUT each line into a VBA Array Function to return each field. This will give you more control over each field you are importing and you could create a SCRUB function that would scrub each field for common errors. Another suggestion is to contact the supplier of your flat file and have them use pipes|||| as a delimiter; thereby eliminating the issue of extra commas... Or have them send it as a tab delimited file. • I am looking for sample code to parse the data record. The Split command does not work because it does not take into account data that has embedded comma within the double quotes. • I don't understand that removing preceeding and trailing quote do. This puts me back where I can not tell an comma in the data string from a comma as a data separator Hi Dennis, Apparently your priority is on findind an algorithm that 'always' works. I think this forum can not give you the answer, you must find it because it is completely dependent on the data you receive. If the problem was (but in fact it is not) the yes-or-no doublequotes around the values, then you could remove them. But the main problem is the comma inside a field, whereas the field separator is also a comma. If the field containing the comma is ALWAYS surrounded by doublequotes, you could manipulate the line by replacing all ', by '; and,' by;' Next step is to parse over the;. But alas, this does not work with a mixture of quoted and nonquoted fields, as your example 2. But instead of the Split function, you can make your own parser. For this purpose I use a homemade function Split_string(cur_text, separator). Antivirus software for samsung galaxy ace plus software. Get free security downloadable Antivirus Samsung Galaxy Ace Plus Apps for your Android device. Free mobile download APK from our website, mobile site. ![]() It returns the part before the separator as function result, and cur_text is reassigned the part after the separator. If the function returns a string beginning with a doublequote, and not ends with a doublequote, you 'know' that the separator was not a true separator, but the inside-comma. In that case you need a next Split_string, and concatenate the first part, a comma, and the second part to reconstruct the field value. Pavitra rishta 4th june 2009 written update of kasam. Pavitra Rishta (International Title: Sacred Ties) is a 2009 Indian soap opera produced. 2009, Indian Telly Awards, Most Popular Actress In A Supporting Role. [3][4] Early life Ankita Lokhande was born on December 19, 1984, in Indore. As Tanmay 2010-11 Geet – Hui Sabse Parayi as Gurvinder 2010-11 Dil Se Diya. Pavitra Rishta- Written Update- June 4th By: N a v i D h e s i For some reason the. Edited by punjabi_qt26 - 04 June 2009 at 6:44pm. Original release, 1 June 2009 (2009-06-01) – 24 October 2014 (2014-10-24). External links. Official website. Pavitra Rishta (International Title: Sacred Ties) is a 2009 Indian soap opera produced by Ekta. 2009, Indian Telly Awards, Most Popular Actress In A Supporting Role, Savita Prabhune. Retrieved 2016-04-06. In this way, by studying the 'typicals' of the data, you can construct the appropriate parser. • Parse the line • Massage the data. (I realize that I have to write this section of the code.) The process seems pretty clear to me: 1. Read 1 line of Text at a time, storing it into a String Variable strLine. Inspect the first character of strLine. If it is a double-quote then find the position of the NEXT double-quote in strLine using InStr() function and extract the first Text Field value using Mid() function. View Microsoft Access FilesIf it is not a double-quote, find the position of the NEXT comma and extract the first Text Field value using Left() or Mid() function. (Optional) After step 2, change the value of strLine using Mid() to the sub String from the first comma after the FIRST Field value extracted to the end. While you can manage the subsequent steps without changing the value of strLine, it may be easier to think and code with the shortened value. Find the NEXT comma indicating the end of the second Field value (numeric) and extract this Field value.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |