Search results

Where's the file? I wanna see if I can get it to insert.

Has anybody here ever created a PowerQuery that does a hideous transformation and then gotten that to import into SQL Server? For all the gringos... I wrote a PowerQuery to parse a Costco Purchase History report, and combine the purchases and the discounts so that the discount is in the same...

Do you have the link for the file so we can try to import it?

In theory, it's possible to use the PowerQuery source in SSIS to import PowerQuery results into SQL Server, but so far I haven't gotten it to work. (I wanted to import a single file at a time, because I wanted to be able to handle the failures by moving the file to a "failed" or "imported"...

My guess is that you did something wrong with the BCP settings. You can set the number of records per batch higher or lower. (Higher will speed it up, but it'll require more memory.) Did you read up on all the switches?

if it's confidential data, I don't think I can help. but if it's not, you could send me a message with one or two PDFs (oh, and the expected output... could be an explanation), I could see if PowerQuery would work any better.

(The point being that they're a trap. What you see is not what you get [stored]). I remember warnings to this effect on a website that's been pretty much dead for 20+ years. The junk that gets put in Access is seriously insane.

Oh, I know!!! Make an account on UtterAccess and ask Volcanics. He uses them all the time!!!

Is this the classic Students and Classes database? If it is, this smells an awful lot like homework. (I remember building this database at university). If this is for class, you really need to figure this out for yourself, because the whole point of this exercise is understanding table design...

Okay, I get it now. No wonder you're so confused! (I would be too!). Here's the problem - to get this to work in your database, you need a different structure. You need basically just two columns, one for PartNumber and the other for ModelNumber. Do you get tons of these files basically in this...

Then couldn't you just tack the insert part onto the front of that? (yes, you need to fill out all the column names in the mysterious "MyTable" and then you just glue them together... insert_sql = "INSERT INTO MyTable(lnquiryID, InquiryDate, InquiredBy,....) " CreateSQL = insert_sql & " " &...

for example?

If they're SQL Server and you have not added any indexes, then performance can be utterly brutal. I had a table once that had maybe 160K records and performance was terrible. So which is it? SQL Server or Access? If the table comes in with a prefix (usually dbo.) then that's SQL Server (it's the...

What if you create a new blank database and import just the 3 tables and the minimum number of queries and forms for us to see the problem? Surely we don't need the entire database.

Oh, okay. This is what I would do... use Excel. Data > Get Data > PDF... then you'll be prompted for a file. The hard part is when PowerQuery cannot easily parse the table. That's not to say that you can't use PowerQuery to do it, but it takes some knowledge of M to do it. The reason I would use...

I'm not sure if Access sneakily adds indexes to columns ending in "ID" etc, but I know for sure that SQL Server does absolutely no indexing on its own. If you want an index somewhere, you have to create one. The one thing your post is missing that would be really helpful is the CREATE TABLE...

CJ, if (and it's a big if!!) your PDF contains tables, and you want only them, then PowerQuery is great. If you want something else, then not so much. PowerQuery basically only reads the tables and then removes all the formatting (or tries to). If you've used Get Data in Excel, you've used...

Excel Doc where? Did I miss it?

I could be wrong, but it sounds like you should be using a combobox with LimitToList set to True and getting the available values from a table. Your solution seems to be to correct the problem after it's happened instead of preventing it from happening in the first place. "make the data entry...