473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Is there an error with my FileSystemObject codes?

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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.     Dim fs As Object
  3.     Dim filename As String
  4.     Dim tsIn As Object
  5.     Dim sFileIn As String
  6.     Dim Text As String
  7.     Dim sqlcre As String
  8.     Dim sqlsta As String
  9.  
  10.     sFileIn = Me.txtImport
  11.     Set fs = CreateObject("Scripting.FileSystemObject")
  12.     Set tsIn = fs.OpenTextFile(sFileIn, 1)
  13.  
  14.     While Not tsIn.AtEndOfStream
  15.         tmps = tsIn.ReadLine
  16.         sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
  17.         DoCmd.SetWarnings False
  18.         DoCmd.RunSQL sqlsta
  19.     Wend
  20.     DoCmd.SetWarnings True
  21.     MsgBox "Finished."
  22. End Sub
Dec 8 '14 #1
9 1251
twinnyfo
3,653 Expert Mod 2GB
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).
Dec 8 '14 #2
jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.      Dim fs As Object
  3.      Dim filename As String
  4.      Dim tsIn As Object
  5.      Dim sFileIn As String
  6.      Dim Text As String
  7.      Dim sqlcre As String
  8.      Dim sqlsta As String
  9.      Dim lLoopCount As Long
  10.      Dim lStartRecordCount As Long
  11.      Dim lRecordCount As Long
  12.  
  13.      lStartRecordCount = DCount("Field1", "Table1") 
  14.      sFileIn = Me.txtImport
  15.      Set fs = CreateObject("Scripting.FileSystemObject")
  16.      Set tsIn = fs.OpenTextFile(sFileIn, 1)
  17.  
  18.      While Not tsIn.AtEndOfStream
  19.          tmps = tsIn.ReadLine
  20.          sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
  21.          DoCmd.SetWarnings False
  22.          DoCmd.RunSQL sqlsta
  23.  
  24.          lLoopCount = lLoopCount  + 1
  25.          lRecordCount = DCount("Field1", "Table1")
  26.          If lLoopCount <> (lRecordCount - lStartRecordCount) Then Err.Raise 12345, , "Record Count out of Sync on line " & lRecordCount 
  27.      Wend
  28.      DoCmd.SetWarnings True
  29.      MsgBox "Finished."
  30.  End Sub
Dec 8 '14 #3
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 " :"?
Dec 8 '14 #4
twinnyfo
3,653 Expert Mod 2GB
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?
Dec 8 '14 #5
It's actually a unicode file, however, I've already checked through and I don't think there are any special characters in it.
Dec 9 '14 #6
twinnyfo
3,653 Expert Mod 2GB
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.
Dec 9 '14 #7
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?
Dec 10 '14 #8
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.
Dec 10 '14 #9
twinnyfo
3,653 Expert Mod 2GB
That will certainly get rid of those strange characters. It is a solution--perhaps not your preferred solution, but a solution, nonetheless....
Dec 10 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

5
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...
9
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...
1
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...
2
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 =...
9
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',...
1
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...
12
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...
1
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...
3
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...
7
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...
0
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.