Hi all,
I'm currently facing an issue with my codes. I am required to import a text file line by line into access, however, when I check the number of records in Access, it does not match the number of lines in the text file.
E.g. My text file may have 519420 lines of text, but my records only show 518785.
I need my entire text file to be imported line by line in the exact order.
This is my current import code: - Private Sub Command3_Click()
-
Dim fs As Object
-
Dim filename As String
-
Dim tsIn As Object
-
Dim sFileIn As String
-
Dim Text As String
-
Dim sqlcre As String
-
Dim sqlsta As String
-
-
sFileIn = Me.txtImport
-
Set fs = CreateObject("Scripting.FileSystemObject")
-
Set tsIn = fs.OpenTextFile(sFileIn, 1)
-
-
While Not tsIn.AtEndOfStream
-
tmps = tsIn.ReadLine
-
sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL sqlsta
-
Wend
-
DoCmd.SetWarnings True
-
MsgBox "Finished."
-
End Sub
9 1251
The only thing I can think of is that you might have some blank lines in the original file. Since there are about 700 lines missing (which is about 1/10th of 1 percent), you may have to experiment with your import. Start with a small portion of your original text file and import all the records. Keep increasing the size of the text file until the number of records imported does not equal the number of lines in the text file. Then, troubleshoot to determine what is different about that particular line that prevent a full import of the line.
Make sense?
I think your code is fine (the same code the experts here have helped you develop).
I agree with Twinnyfo, you'll need to do some creative troubleshooting to find out what is happening.
Have you looked at the file in notepad with Word Wrap turned off? That would be a quick visual check for blank lines.
You could also put in a couple counters in your code and throw an error or break when they get out of sync: - Private Sub Command3_Click()
-
Dim fs As Object
-
Dim filename As String
-
Dim tsIn As Object
-
Dim sFileIn As String
-
Dim Text As String
-
Dim sqlcre As String
-
Dim sqlsta As String
-
Dim lLoopCount As Long
-
Dim lStartRecordCount As Long
-
Dim lRecordCount As Long
-
-
lStartRecordCount = DCount("Field1", "Table1")
-
sFileIn = Me.txtImport
-
Set fs = CreateObject("Scripting.FileSystemObject")
-
Set tsIn = fs.OpenTextFile(sFileIn, 1)
-
-
While Not tsIn.AtEndOfStream
-
tmps = tsIn.ReadLine
-
sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL sqlsta
-
-
lLoopCount = lLoopCount + 1
-
lRecordCount = DCount("Field1", "Table1")
-
If lLoopCount <> (lRecordCount - lStartRecordCount) Then Err.Raise 12345, , "Record Count out of Sync on line " & lRecordCount
-
Wend
-
DoCmd.SetWarnings True
-
MsgBox "Finished."
-
End Sub
Well, I managed to find some lines that were not inputted into the the table.
The lines are:
" :61:1410311031D22,50NSC-SW000000//SC-SW"
" :86:M04"
" 000000"
" :62F:C141031USD746743,53"
" :64:C141031USD746743,53 )]~"
Is there a possibility that the code is unable to read lines that start with " :"?
Hmmmmmmmmmmmmmmmm, I was able to insert those lines into my tables......
Are you sure these are not wrap lines (perhaps part of a line that is more than 255 characters)?
There does not appear to be anything incredibly strange about your text.
This is an ASCII file, not Unicode? No hidden special characters or anything like that?
It's actually a unicode file, however, I've already checked through and I don't think there are any special characters in it.
Shawn,
The Unicode may be the issue. Have you tried changing the data type of the field in the table to "Memo" and setting the TextFormat Property to Rich Text?
Keep in mind that this may significantly bloat your DB during the import, but it may allow you to import your text.
Hi twinny,
I've already tried those methods and it still can't work. I know this is highly unlikely, but could it be a hardware/software issue?
Well, I might have found a solution, but it'll mean more work for the user. I have to open the text log file in notepad++, copy the entire thing and paste it in a new file in notepad++ and import that file instead.
That will certainly get rid of those strange characters. It is a solution--perhaps not your preferred solution, but a solution, nonetheless....
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mark Oueis |
last post by:
I've been struggling with this question for a while. What is better
design? To design functions to return error codes when an error
occures, or to have them throw exceptions.
If you chose the...
|
by: MDW |
last post by:
I'm not sure if this is a coding problem or an IIS settings problem.
I've enabled IIS on my local machine (Windows XP SP2) so I can do some ASP
development prior to rolling out my Web page to a...
|
by: Alex |
last post by:
Hi,
I am using DBD::Pg in some of my scripts. I want to customize the error
login based on the error received.
While I am happy with the Errstr message I want to take specific actions
depending...
|
by: Ali |
last post by:
Binding error
< SQLSTATE = 51002, SQLCODE = -000000805 >.
< DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME DBD2SYSTEM..SB0A0Q00.174
< PLAN CGEY001D. REASON 03 >.
< DSNT418I SQLSTATE =...
|
by: kermit |
last post by:
I keep seeing that you can use the FileSystemObject in either VB script, or
Javascript on an aspx page.
I added a refrence to the scrrun.dll
I added importing namespaces for 'System.Object',...
|
by: tschulken |
last post by:
I am developing a web app in vb.net. I created a base page class for my
pages and in the page_error event I wrap the exception to collect some
common data elements known to all pages so that this...
|
by: mast2as |
last post by:
Hi everyone... I have a TExceptionHandler class that is uses in the
code to thow exceptions. Whenever an exception is thrown the
TExceptionHander constructor takes an error code (int) as an...
|
by: RdS |
last post by:
Hello,
I have been searching for what createfolder can return or does return.
Where on MSDN can I find what a MS method returns? I have searched for
filesystemobject.createfolder errors, error...
|
by: RdS |
last post by:
Where do I find return codes for MS methods. For instance the
filesystemobject methods. I need to know what methods return (error codes,
etc. and what they mean). I have searched MSDN and of...
|
by: kooroshkdt |
last post by:
hi everyone,
i'm not beginner but i learn programing in hack kind, i don;t have any idea about my problem(in C++ wrote)!!! Read Plz post if u have any Idea, i want to here it, it will be...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |