Field names auto-quoted

[Anonymous]

Hello,

I am attempting to import a tab-delimited text file. It has some single quotes at the beginning of some fields - I don't know why, but that's how it is - And when using the default MSQ quoting, several fields are interpreted by QlikView as a single field. When I select "No Quotes" the fields are properly recognized.

However, when it's set to "No Quotes" QlikView is putting double quotes around every single field name for some reason -

1) Why is it doing that?

2) How can I get it to not do that?

3) Will it still work if I manually remove the quotes from the field names or do

"Field" as Field

on every line in the script?

There are a LOT of fields and I'd rather not have to do this manually - It doesn't make sense to me why QlikView is doing this.

[#2]

Could post the sample excel file how it looks.

[#3]

Hi,

Qlikview may be putting double quotes to just identify fields.

Can you check and let us know that if you load fields just as it is with double quotes put by Qlikview, then does your field name in data model (when these fields are loaded) also comes with double quotes?

I hope it will not and then it should not be an issue for you.

.....

Ashutosh

[Anonymous]

The data is not in files, it's being served from a web server. Here's the URL: http://www.faa.gov/airports/airport_safety/airportdata_5010/menu/nfdcrunwaysexport.cfm?Region=&Distr...

Ashutosh, Thanks for the idea - Unfortunately, right now it's not loading at all. It says "Field not found: SiteNumber". This appears to happen regardless of the quoting, however... I pulled the quotes from around SiteNumber and it's still failing on SiteNumber (the first field). Odd.

I also attempted to re-do some other parts so that I could do a Load * on this file rather than specifying field names. The data loads, but the field names all have quotes and thus it does not associate with the other tables I have.

[Miguel_Angel_Baeyens]

Hi,

Hope I'm not missing something. Using MSQ will do, leaving the field names without quotes and values seem to be fine (note that I'm using Spanish regional settings, so numbers have a comma as decimal separator instead of thousand separator, but copying the script and pasting into your own file should work just fine). Anyway, field names between double quotes are not a problem.

Double quotes are used in the script when the field may have some blanks, spaces, underscores or some other non ascii standard characters (MySQL for example adds `field`, MS SQL Server uses [Field]...). And that should work fine. As far as the script is concerned, "Field" is equal to Field. The issue would be if the actual field name had double quotes within.

By the way, if I open that site in my browser, I get a XLS file that when opened returns the same results as the application attached.

Hope that helps.

Miguel

[#6]

Hi,

I just tried to load your web file through web file load wizard and I don't get any double quotes around the field names and it loads fine.

I am just pasting the load script that I get.

LOAD

     SiteNumber,

     State,

     RunwayID,

     RunwayLength,

     RunwayWidth,

     RunwaySurfaceTypeCondition,

     RunwaySurfaceTreatment,

     PavementClass,

     RunwayWeightBearingCapacitySW,

     RunwayWeightBearingCapacityDW,

     RunwayWeightBearingCapacityDT,

     RunwayWeightBearingCapacityDDT,

     EdgeLightsIntensity,

     BaseEndID,

     BaseEndTrueAlignment,

     BaseEndILSType,

     BaseEndRightTrafficPattern,

     BaseEndMarkingsType,

     BaseEndMarkingsCondition,

     BaseEndArrestingDeviceType,

     BaseEndPhysicalLatitude,

     BaseEndPhysicalLatitudeS,

     BaseEndPhysicalLongitude,

     BaseEndPhysicalLongitudeS,

     BaseEndPhysicalElevation,

     BaseEndCrossingHeight,

     BaseEndGlidePathAngle,

     BaseEndDisplacedLatitude,

     BaseEndDisplacedLatitudeS,

     BaseEndDisplacedLongitude,

     BaseEndDisplacedLongitudeS,

     BaseEndDisplacedElevation,

     BaseEndDisplacedLength,

     BaseEndTDZElevation,

     BaseEndVASI,

     BaseEndRVR,

     BaseEndRVV,

     BaseEndALS,

     BaseEndREIL,

     BaseEndCenterlineLights,

     BaseEndTouchdownLights,

     BaseEndObjectDescription,

     BaseEndObjectMarkLight,

     BaseEndPart77Category,

     BaseEndObjectClearSlope,

     BaseEndObjectHeight,

     BaseEndObjectDistance,

     BaseEndObjectOffset,

     ReciprocalEndID,

     ReciprocalEndTrueAlignment,

     ReciprocalEndILSType,

     ReciprocalEndRightTrafficPattern,

     ReciprocalEndMarkingsType,

     ReciprocalEndMarkingsCondition,

     ReciprocalEndArrestingDeviceType,

     ReciprocalEndPhysicalLatitude,

     ReciprocalEndPhysicalLatitudeS,

     ReciprocalEndPhysicalLongitude,

     ReciprocalEndPhysicalLongitudeS,

     ReciprocalEndPhysicalElevation,

     ReciprocalEndCrossingHeight,

     ReciprocalEndGlidePathAngle,

     ReciprocalEndDisplacedLatitude,

     ReciprocalEndDisplacedLatitudeS,

     ReciprocalEndDisplacedLongitude,

     ReciprocalEndDisplacedLongitudeS,

     ReciprocalEndDisplacedElevation,

     ReciprocalEndDisplacedLength,

     ReciprocalEndTDZElevation,

     ReciprocalEndVASI,

     ReciprocalEndRVR,

     ReciprocalEndRVV,

     ReciprocalEndALS,

     ReciprocalEndREIL,

     ReciprocalEndCenterlineLights,

     ReciprocalEndTouchdownLights,

     ReciprocalEndObjectDescription,

     ReciprocalEndObjectMarkLight,

     ReciprocalEndPart77Category,

     ReciprocalEndObjectClearSlope,

     ReciprocalEndObjectHeight,

     ReciprocalEndObjectDistance,

     ReciprocalEndObjectOffset,

     RunwayLengthSource,

     RunwayLengthSourceDate,

     BaseEndGradient,

     BaseEndGradientDirection,

     BaseEndPositionSource,

     BaseEndPositionSourceDate,

     BaseEndElevationSource,

     BaseEndElevationSourceDate,

     BaseEndDisplacedThresholdPositionSource,

     BaseEndDisplacedThresholdPositionSourceDate,

     BaseEndDisplacedThresholdElevationSource,

     BaseEndDisplacedThresholdElevationSourceDate,

     BaseEndTouchdownZoneElevationSource,

     BaseEndTouchdownZoneElevationSourceDate,

     BaseEndTakeOffRunAvailableTORA,

     BaseEndTakeOffDistanceAvailableTODA,

     BaseEndAcltStopDistanceAvailableASDA,

     BaseEndLandingDistanceAvailableLDA,

     ReciprocalEndGradient,

     ReciprocalEndGradientDirection,

     ReciprocalEndPositionSource,

     ReciprocalEndPositionSourceDate,

     ReciprocalEndElevationSource,

     ReciprocalEndElevationSourceDate,

     ReciprocalEndDisplacedThresholdPositionSource,

     ReciprocalEndDisplacedThresholdPositionSourceDate,

     ReciprocalEndDisplacedThresholdElevationSource,

     ReciprocalEndDisplacedThresholdElevationSourceDate,

     ReciprocalEndTouchdownZoneElevationSource,

     ReciprocalEndTouchdownZoneElevationSourceDate,

     ReciprocalEndTakeOffRunAvailableTORA,

     ReciprocalEndTakeOffDistanceAvailableTODA,

     ReciprocalEndAcltStopDistanceAvailableASDA,

     ReciprocalEndLandingDistanceAvailableLDA

FROM

[http://www.faa.gov/airports/airport_safety/airportdata_5010/menu/nfdcrunwaysexport.cfm?Region=&Distr...]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[#7 · Accepted solution]

I see the problem. The only way I could get around it is by setting no labels (to get all the excel file fields properly) and removing the  header line.

What this means is that you will have to name the fields mnaually but is a one time deal.

To get around the single qoute that is put in the excel (by the way this is done to make the field value text in excel) is use "Right" function.

I have attached the sample qvw.

Hope this helps.

[Anonymous]

Miguel and Ashutosh,

The problem may not be obvious, as it will load - However, the data is incorrect. If you put the URL into the wizard, look under "RunwayID" when it's set to no quotes (that is what it should be - format is ##/##), and then set it to MSQ and the next several fields are all put into the RunwayID field, which is incorrect. It'll load, but the data isn't right and all the fields are off.

qlikdisqruser1, it appears that you've gotten it to load correctly - Thanks! I'll just have to put the field names back in.

Now the question is, why does QlikView behave in such a way? The workaround works, but I don't feel as if I should have to perform a workaround. Sigh.