1

I have a collection of excel spreadsheets that are formatted... less than ideally. I'm testing out some solutions involving SQLBulkCopy and OleDB, but I'm a bit concerned about how to handle the format of this sheet.

I was considering writing a custom Insert statement, but would like to see if there may be some easier way to implement a heuristic.

Below is a sample of the data I will be parsing: excel spreadsheet

The highlighted columns are the ones I'll be loading into the two tables. One table will hold order #s, and the other table will hold all the lines below that order number.

Any suggestions on tackling this would be lovely. The excel sheets are hand entered, so some weird cases exist (one order number with multiple carriers, which imposes the question of whether I should treat the first row with the order number as a line in the database structure I designed.

I'm implementing this importer within VB.net, to my dismay, to avoid being looked at funny by my coworkers :).

Eric Lang
  • 41
  • 6

2 Answers2

2

One approach would be to save the worksheet to a text file (e.g., CSV) and then use AWK to split it at the empty row. Some examples are in this SO answer: Bash how to split file on empty line with awk

You could then import the CSV files directly into the database.

rd_nielsen
  • 2,407
  • 2
  • 11
  • 18
  • One issue is sometimes the rows aren't empty, and I was intending on writing some type of logic to determine whether the row is actually symbolically empty or not. – Eric Lang Feb 21 '20 at 19:52
  • That logic might also be able to be implemented in AWK. – rd_nielsen Feb 21 '20 at 20:02
2

Amusingly , if I wrote anything in VB.NET I'd definitely get looked at funny by my coworkers

So I'd use a library called EPPlus to read the excel and not have to worry about converting it. How you do the blank line detection is an open question- checking that the Value of ten cells on the row is Nothing or Empty would suffice. Then take the next row as your parent, and proceed with subsequent rows as children until the next blank

Take a look at this answer for more info on how to detect blank rows in Excel- if you get stuck turning any of the c# into vb shoot us a question. Online converters exist because the two languages are the same thing under the hood

Caius Jard
  • 72,509
  • 5
  • 49
  • 80