Search results

Yep I'm confident of input integrity, it is produced by another computer system. I don't think there will ever be two commas. So this split function would do it, would that be quicker seeing as it is still VBA? Is it VBA?

Oh sorry, I should have included that. My temporary table from my .csv import gives names like "Baker, Mark" and I want to split them into first and last names before I insert them into my actual database. Some are company names though, these have no comma, and are stored in the last name field.

Hey guys, can an if statement be used to parse/split a name in SQL? I'm assuming it would be quicker than VBA. Here's my current code: ' My major worry is that this could be a lot quicker if done in SQL, but that is very complex to design Do Until .EOF '...

Hi everyone, this is a great site and it has been very helpful, but I'm not sure I am tackling my problem the right way. I'd appreciate any insights. I have a database of insurance payments which is separated by object type(advisor, Policy, Payment, Customer etc). I import a .csv file into a...

yep, I think that name parsing is going to be a nightmare that I will put off for a while. Although I did find something near the solution here: http://www.tek-tips.com/faqs.cfm?fid=6468 Are recordset modification noticeably slower than SQL queries? I've now moved on to making a recordset for...

I still need to loop through my temporary table to split the advisor and customer name fields up though. eg, "Baker, Rod" has to be "Baker" in one column, and "Rod" in the other. Names in the .csv come as one. It is hard to read them in, especially things like "Mr & Mrs I A Farva". how would...

Oop no, problem solved, seemed the table name needed []'s around it

Yeah I think you might be right in the textstream being the way, but I really need to show some results, even if they are slow, and I'm quite far down the track now. Too many ways to skin the cat! I've got a temporary table cleaned up from an import call, and now I'm heading on the road of...

Ok, so to append with all this complicated formatting, I should call many smaller SQL queries? i.e add advisors who aren't in system already, add customers who aren't in system already, add policies to link these two, add payments to link all three Cheers again for the help

Oh awesome galaxiom that did the trick, just had to rearrange the module. Sorry I've been distracted by minor problems here, back to the original topic. Can an SQL statement like this: INSERT INTO Advisors ( LastName ) SELECT [TempTable-SovImport].AgentName, * FROM [TempTable-SovImport] WHERE...

Cheers for that, it seems to definitely be a good idea to keep the tempTable out of the front end, but I'm going to postpone doing that because at the moment I still don't have anything useful working at all. I'm currently running this SQL DELETE statement to delete the irrelevant rows before...

So I think, using Dave's solution from an earlier thread, I am on the right track. From a form, I am using transferText to import to a temporary table, then parsing that with a recordset. My temporary table looks exactly the same as the .csv file, only in access, and with relevant headers I...

The headers aren't really important for me, I've made an import specification which creates its own headers, but for example, the first column of the table is an agent name like "Baker, Rod" I want to be able to parse that, and retrieve first and last names from it. Then I would need to check...

Oh that is such a shame to rewrite my method. Is there no way to parse a record before the insert statement?

I really wish I could parse it with VBA, because the date, pay run ID etc are all within strings in the first few rows, and I would need to take substrings of these to get the date of the payment. Is this possible? I have created a simple query which returns all rows with something in the first...

Hi again, I've been doing a bit of research around this, and rather than re-starting old threads or dragging mine off topic, I thought I should start a new one. My situation is keeping track of insurance payment details emailed in .csv format. My database is set out like this: ADVISOR...

Yep I think that's the ideal way thanks. The leap for me was calling an import specification from the vba, and the checks it needs, like this: ' Check that there is only one period in the filename Dim numPeriods As Integer numPeriods = Len(Dir(sFile)) - Len(Replace(Dir(sFile), "."...

Did you end up creating multiple append queries? If so, how do they link up? I have a single temporary table from a transfertext operation, and I want to be able to parse through it and select which rows I want to import. I think I will need to use your solution, as the data from each row will...

Ok Dave, that sounds logical in theory then but how does it work? Does transfertext create a temporary table if you put an unkown name into it? As above, I need to somehow put the data across many tables, and the examples I have seen seem to only use one table, and have .csv's full of relevant...

Hey again everyone, thanks for the help. I found this template which I am trying to modify for my use on databasejournal.com (can't post link yet sorry) I will post my code here instead Public Function importSovereign(sFile As String) As String On Error GoTo ProcessFileImport_Error '...