GSA 5.2.7. csv as datasource

[Björn Schaefer]

Hi everybody!

the format csv is not listet yet as an option for connecting a new datasource. Unfortunately there are some external Systems, which may deliver data only in csv. Is there (or will be) any possibility to connect csv as datasource without converting it first to an excel format?

 

[Joery Korobejnik]

Ho Bjorn,

The Excel data source also supports .csv files, you will get an additional option when the .csv file is recognized. See also the specific topic in the : Online Help



Kind regards,

Joery Korobejnik

 

[Björn Schaefer]

Hi Joery

that was way to easy ;-) thanks a lot. But there is another problem with the csv:

within the file, there is something like this:

Achtergarten 10,29348,Eschede,060005010,M,2,,Cluster Nord-West Eschede Kern,

After connecting the csv as data source and making a request, this is shown in the result list:



As you can see: the business field is defined as text (string), but the value is different to the original text: 060005010 vs 60005010. The leading Zero ist missing and I have no Idea, how to save the correct information, but unfortunately, the leading 0 is needed within further steps.

 

[Joery Korobejnik]

Hi Björn,

Could you create a sample of this .csv for me (with no personal information) so that I can see if I can solve this within the project configuration (I can also see that one record under this one the 0 is there (probably because there is also a letter in the string).

Kind regards,

Joery

 

[Björn Schaefer]

I hope I did it right: there are four rows with different data. The Value SVOObjekttschlüssel does not necessarely have to start with a leading 0, but always need to have 9ciphers + an optional 10th for the letter (Hausnummerzusatz).

rows with a letter at 10th place are imported / shown correctly, 9cipher long ones with no leading 0 will work also...

 

[Joery Korobejnik]

Hi Björn,

I can reproduce it, let me see if I can figure it out if there is a workaround or if this is a bug that needs to be fixed in the software. I also have an idea about how we can ensure the 9 position by using combination of the If / Length and String.RegexReplace expressions to workaround this issue.



Kind regards,

Joery

 

[markvanderhurk]

Hi Björn,

We have taken a look into the sample you provided. First: you have encountered a bug in the Excel/CSV featuresource. We will report this to our product team.

Meanwhile, we have a suggestion for a workaround. From the sample you provided, we assumed that a couple of situations can happen:
- one or more trailing zeros could be lost
- numbers are always max 9 characters, if applicable combined with leading zeros
- numbers in the attribute can be followed by a letter (making a combination of 10 characters)
Of course we are not sure that this is a valid assumption, but you can tweak the workaround if necessary

Please try this as an attribute definition:
{
var numlen = Sheet1.SVOObjektschluessel.Length;
var numtrailingzero = IF(numlen < 9, 9 - numlen, 0);

var replacestring = "000000000";
var zerostring = replacestring.SubString(0, numtrailingzero);

var finalstring = Concat(zerostring + Sheet1.SVOObjektschluessel);

return finalstring.SubString(0, 10);
}

Best regards, Mark

 

[Björn Schaefer]

Hi Mark

this seems to work properly many many thanks for this solution.

 

[markvanderhurk]

Great, you're very welcome!

 

[Frank Pistorius]

Hi Björn,

A bug was reported for this issue. Unfortunately, without further meta-information on the column, the indicated values are provided by the Excel engine as numbers.
We do treat the full column as a string column since we encountered values that are strings, but the underlying engine still removes the leading zeros from number-like values.

We will of course look into possibilities to circumvent this behaviour, but in the meantime, the (nice!) expression that Mark included in his post should help you out.

Regards,

Frank

 

[Björn Schaefer]

thanks a lot !