UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can I Whinge Here?    
 
   
haresfur
post Feb 11 2018, 10:03 PM
Post#1



Posts: 239
Joined: 4-April 12
From: Bendigo, Australia


Is this an appropriate place to vent about someone sending out periodic data updates as .csv files where:

  • The field names are not all valid because they contain characters that are not allowed
  • The second row is dashes for each field like, "----"
  • Null fields usually but not always contain "NULL"
  • Some text field are delimited with quotes but some are not
  • The second last row is blank
  • The last row is something like "(445135 rows affected)"


--------------------
-- Evan
Go to the top of the page
 
WildBird
post Feb 11 2018, 11:14 PM
Post#2


UtterAccess VIP
Posts: 3,306
Joined: 19-August 03
From: Perth, Australia


That's a report, not a CSV. What system are the files coming from?

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
haresfur
post Feb 12 2018, 12:03 AM
Post#3



Posts: 239
Joined: 4-April 12
From: Bendigo, Australia


Lord only knows. I'm pretty sure it is generated from a govt agency's database by a subcontractor.

They have changed a few things recently and messed up at least one table. But yes they supply it as a csv file, however they got to that.

--------------------
-- Evan
Go to the top of the page
 
JonSmith
post Feb 12 2018, 05:46 AM
Post#4



Posts: 3,261
Joined: 19-October 10



Consider yourself lucky thats all you need to deal with.

In the organisation I work for they wanted to create a standard format for file received from external sources and processed by our SSIS. XML was chosen (smart choice) but at the time we had lazy developers in one of the main systems and they didn't want to re-write their import routine.
They demanded an XML file with line indents and formatting and the CSV data to simply be dumped into a single node formatted as CDATA[] with line breaks at the top and bottom. This meant the lazy developer could use the exact same import scripts as before but tell it to skip the top 5 rows and the bottom 3. It creates an pretty much unusable file in terms of it being XML since its putting an entire table of data inside a single field. To make matters worse some of the CSV's embedded are 'fixed width' only they aren't. They start fixed width but when numeric values come in they mess it all up.

See attached a screenshot with the data obscured (Data is already mocked up for testing so sensitivity is low as all the values are garbage but best to keep any codes or data sources private).
Attached File(s)
Attached File  XML_Lite.jpg ( 306.9K )Number of downloads: 13
 
Go to the top of the page
 
haresfur
post Feb 12 2018, 05:06 PM
Post#5



Posts: 239
Joined: 4-April 12
From: Bendigo, Australia


Ouch

--------------------
-- Evan
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd February 2018 - 11:19 PM